Data Driven Query Task in DTS for updating data from Excel into SQL Server table

This is a very common issue we face.

There is a Excel sheet with data that needs to be imported into SQL Server table. The SQL Server table has unique key/primary key constraints on a key column (e.g. InspectorID). However the Excel spreadsheet can contain duplicates since this either being generated manually or coming from a different data source (typically a data warehouse). To be able to create a DTS script that uploads data from the Excel spreadsheet into Inspector table :

I divided the Inspector table upload process into 2 steps. In the first step I inserted all the distinct InspectorID’s from Excel spreadsheet into the Inspector Table using the TransformData Task step in DTS, and for other columns that did not accept nulls, I used default values for these fields in the insert statement.

After this I used a Data driven query Task to update the remaining fields of the Inspector table from the spreadsheet. Working with this task was quite tricky, since at first glance it would appear as if you are updating the Inspector table with the fields from the Inspector table itself. However, once you create a one-one mapping between the fields in the Excel spreadsheet and the Inspector table using the ActiveX Script in Transformations tab and set the query type to DTSTransformstat_UpdateQuery instead of DTSTransformstat_InsertQuery, it automatically behind the scenes maps the source (Excel) fields to the destination (Inspector table) fields.

FYI, the various options available in the Transformations tab include: ActiveX Script, Copy Column, DateTime String, Lowercase String etc..

First in the Source you need to select the Connection1 (Excel) and then choose the Excel Spreadsheet from where the data is retrieved.

Second in the Bindings tab select the Inspector table from Connection2 ( SQL Server connection)

Now if the column in the Excel Spreadsheet does not Map exactly with the column in Inspector table, then you can make use of the Lookups tab in either the Transform Data Task or the Data Driven Query Task.

Thus you can create a lookup query such as : select InspectorTypeID from InspectorType where Type = ?. If this Lookup was named TypeLookup.

Then you will need to modify the ActiveX Script in Transformations tab as follows:

Function Main()
DTSDestination("InspectorID") = DTSSource("ID")
DTSDestination("InspectorName") = DTSSource("Name")
DTSDestination("InspectorTypeID") = DTSLookups("TypeLookup").Execute(DTSSource("InspectorType"))
Main = DTSTransformstat_UpdateQuery
End Function

If it is a TransformData Task then the Function Main should be set to the value : DTSTransformStat_OK

Then in the Queries Tab, select Update query and use the following query to update Inspector table:
update Inspector
set InspectorName = ?, InspectorType = ?
where InspectorID = ?

Then click on Parse/Show Parameters then Map Parameter1 to InspectorName, Parameter2 to InspectorType and Parameter3 to InspectorID.

Note that the dropdown for Parameters displays the columns from the Inspector table and not from the Excel spreadsheet. Thus you may wonder how would we get the values from the Excel spreadsheet to update the Inspector table. The trick is that since we have one-one mapping between the columns in the Excel spreadsheet and the Inspector table as we defined in the ActiveX Script in the Transformations tab, thus we get the values from the excel spreadsheet behind the scenes.

Thus this task updates the InspectorName and InspectorType number fields from the Excel spread sheet to Inspector table for all the unique/distinct InspectorID’s that were inserted in the previous “Transform Data Task” step.