The order of the configurations used in the development studio is not the same as the order used by DTEXEC, specifically in relation to parent package variables.
Parent Package variables always have the lowest priority no matter what order you set in the configuration manager.
Microsoft have recently added a note to BOL on this subject here
Amazon Link: Microsoft SQL Server 2005 Integration Services
I recently saw some forum posts on SSC.com asking if there was a way to bulk import DTS 2000 packages into a SQL 2005 database.
The answer is that you can use the DTSBackup utility available for download at www.sqldts.com. Its a lightweight and extremely useful tool that can save hours of mouseclicking, and it works on both SQL 2000 and 2005.
Amazon Link: SQL Server 2000 Developer Edition
Normally in SSIS when we want to issue a SQL command against each row in a dataflow, we use the OLE DB Command component. When executed against SQL Server the command normally takes the form:-
UPDATE dbo.TableName SET ColumnName1 = ? WHERE ColumnName2 = ?
Where “?” can be mapped to the incoming columns in the order they are written.
However you will find that when you are using this component against an Access database, that this syntax will not work. You will get an error saying:
There is more than one data source column with the name “?”. The data source column names must be unique.
Amazon Link: Microsoft® Office Access™ 2007 Inside Out (Inside Out (Microsoft))
Given the problems inherent in loading excel files with SSIS, I thought it might be useful to share with you some code to get you started on interrogating it’s schema prior to running your Execute SQL control tasks or your dataflows.
This is especially useful for me as it allows me to test column names, worksheet names and datatypes etc prior to attempting to load the data (I have found that users have a tendency to format and rename sheets without realising the impact it has on the import). There are other uses too…
