Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

A dynamic SSIS package with Configuration file

A dynamic SSIS package with Configuration file

A dynamic SSIS package with Configuration file


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?


RE: A dynamic SSIS package with Configuration file

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close