Today I have been modifying a very repetitious SSIS package which does bulk import of data form a flat file. The Data Import Wizard is a pain when you want to import the same format but from different source files, especially since it is unstable and tends to crash when you make changes and try to re-run its SSIS package.
So I created a package using the Wizard, then created a new Integration Services project in Visual Studio 2008 (no word on when support will be added for SQL Server projects in VS 2010?). I imported (add existing item) the package the Wizard created and started to much around with adding variables so that I could set the input file path and the destination table name before executing the SSIS package in question.
The power here is we only have to set column definitions and the schema of the destination once, rather than having to re-jig every time we go to (manually) import from a flat file. Below is what the wizard-generated SSIS package looks like:
The package created by the Wizard uses a hardcoded table name (for the destination) and input file paths (pointing to the source flat file) – so in order to work around this I had to do the following:
– change the hardcoded destination table (which is defined in a Script task) to something generic
– add a second SQL Task which renames the generic table to the value we want from config
– add a configuration for the package (so I can set the source path and the destination table name)
So here is the Control Flow of the package, now modified, from the original rendition:
The second task simply calls a system stored procedure to rename our “generic” table (in this example called, tmp) to a name set by configuration:
Note here that I have created a User defined variable which is then set via configuration. Speaking of configuration, how does one go about establishing an external config file for an SSIS package? Actually, it’s quite easy..
Right click your package designer and select “Package Configurations…”. Create a new configuration, and use the wizard to specify the location to store the file, and the settings you wish to override via the configuration. It’s all very simple, I’m not going to go into detail here. You can see the settings in the screenshot below.
You can also set the values of your Data Source components – in the case of the flat file connection manager, this is the ConnectionString property. Anyhow, hope this entry helps any of you who are lost in overriding SSIS settings using configuration!
R
