Using Excel as a Source for your Data Generation Plan in Visual Studio 2010

I like to use DataDude for almost anything related to SQL Server.  I use the data generation feature quite a lot.  Most of the times, I am happy with the string randomly(RegEx) created by it.  However, I encounter a situation in which I needed the random source to be in a group while still preserving the randomness of the wizard.

Of course, I could have created a SQL server temp table and use that as my sequential data bound field. However, I wanted to try to see if I could use a different source.

I’ve created a simple CSV with 3 columns, that I’ll use to populate my SQL server project.

Notice the name of the columns, and also the name of the worksheet.  You will need those for later.

Assuming your SQL Server Database has a table named CommtDefn.

Import the schema into a SQL server project in Visual Studio 2010. After you have imported your schema in your solution, you would be ready to populate this table with data. 

First step, is to add a new data generation plan, then select the table CommtDefn and, select the first column.

Your Data Generation plan would initially look like this

Select the first column and choose the Sequential data bound generator option.

Under the properties window, select <Add new connection..>

then on the Data source screen, select <other>.  For the data provider, use the ODBC provider, and not the OLE DB Provider as instructed in MSDN.  Now click Continue

On the connection properties, select the Use Connection string. NOTE: the trick in here is to specify the type of connection we are going to use

So in this box type the following:

Dsn=Excel Files

Now, click the build button. A dialog box will pop up asking you to select the Data Source, click OK to dismiss this dialog box. This action will bring another dialog box where you can specify the actual excel file you want to use.!!

Select your Excel worksheet, and then click OK.  The Use Connection string field is populate with the proper connection string:

Dsn=Excel Files;dbq=C:\vsProjects\2010\PowerShell.SQL\PowerShell.SQL\Excel CSV\Committees.xlsx;defaultdir=C:\vsProjects\2010\PowerShell.SQL\PowerShell.SQL\Excel CSV;driverid=1046;maxbuffersize=2048;pagetimeout=5

Click on the Test Connection button, make sure it is successful. Click OK twice to close all dialog boxes.

Go back to your Properties window and write the following query

SELECT * FROM [CommitteesDefinitions$]

In here, don’t forget to enclose the name of the worksheet in parenthesis and also you need to add the “$” at the end of it.

Your screen should now look like this

Now if you go back to the data generation screen, you will see that the Generator Output column has been populated with the columns extracted from the Excel worksheet.!!

* BTW, there is a MSDN article on how to generate data from an excel spreadsheet, but after following those instructions I could not get that to work with VS2010 data generated plans.