A dynamic SSIS package with Configuration file

[spangeman]

Hello

I've been using a combination of SQL Scripts and SQL 2000 DTS packages for
data transfer work for the past 18 months.

I would like to start using SSIS instead but am not really sure where to
start. I have managed to do a simple import in the SSIS environment but I
would really like to do is........


Develop a package which can take a configuration file as input which
stipulates sets of transfers for the overall package. The configuration file
would be used to define the following for each transfer in the package:-

A Data source (e.g. CSV files, SQL DB source with a SQL query)
Different SQL Server Destinations Tables
Column mapping between the source and destination
Defaults for columns
Conversion rules for columns e.g. if columnA value = '45' set ColumnA = '105'

(The config file needs to be something like an XML file or an excel sheet
which I can take out of the office to configure)


I'd like the package to deal with the following generic issues, which I have
found with data transfers in the past.

1. If a columns destination is a date and the source is not a date change it
to the column default

2. If a columns destination is a mandatory field and the source is empty
change it to the column default

3. If a columns destination is a mandatory field and the source is empty
change it to the column default

4. Ignore all truncation errors, if a field is too long simply cut it off at
the field length

5. If a columns destination is a field with a FK attached to it the source is
empty change it to the column default.

My question is to achieve the above can I use SSIS?
And if I can use SSIS what techniques do I need to learn for this?


Cheers
Spangeman

 

[gradley]

Hi Spangeman,

Items 1-5 can be handled during the dataflow process of SSIS. I will warn you though that SSIS is less lenient on invalid data types. If you plan on receiving values other than NULL in a date field, you should probably define the import field as a string first and convert it during a Derived column task within the dataflow"

1. Define input as string set the "Retain Null Values" checkbox in the flat file source. Add a Derived Column to convert the value to a datetime variable.
4. Specify to ingore all Truncation Errors in the Flat File Source. It's under the "Error Output" tab.
5. Use the Lookup Task for FK retrieval. Make sure you specify "Ignore Failure" in the "Error Output" tab of the Lookup. This will handle Non-matching lookups.

As for the Configuration File. SSIS comes with Configuration file capability which I think is ideal for DB connections. SQL Server Mag has had some great articles on using Configuration Files with SSIS. I've created a separate one for each SQL Connection. By doing this, they can be reused for other packages. There are many great suggestions on the web for using these files. As for some of the other things you are looking to do, I would suggest soft coding them in tables rather then config files since I believe the main purpose of Config files are environment differences.

This should give you a good jump start.

I'm still pretty new to the SSIS stuff (as everyone else is), but through trails and lots of errors, I'm feeling very good about the new DTS (SSIS) as a application with a lot of flexibility.

My only suggestion is to be patient. You will find alot of things don't work the same as they did in DTS.

Good Luck!