Change Flat File Source Dynamically

I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.

Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?

Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?

Thanks,

Chris

You can set the ConnectionString property of your flat file connection managers to be based on an expression, which is in turn based on a package variable. You can then set the value of the variable from the command line when you run DEXEC.

You can also use the Foreach Loop container to loop through all of the files in a given folder, and use the package variable as the loop enumerator, so that it will be automatically set to the name of the current file. If you know that files that are of a given format from a given client will always be in a specific folder, this might eliminate the need to have a custom UI and rely on operator input to kick off the ETL process. I don't know if this makes sense for your environment, but it sounds attractive to me.

I have not personally used the .NET API to execute packages, so I cannot comment on that approach.

|||

Matthew,

Thank you! I knew that should be possible, but, I couldn't figure out how. Based on your hint (expression) I was able to find the expressions section on my Connection's properties pane. I added an expression the ConnectionString (path) to a variable. It works!

One small oddity... I wanted to set the variable in a Script Component for testing (rather than command line). However, I couldn't set the Script Component as the first object on my Data Flow since the Flat File Source object will not take a predecessor. Instead I created another Data Flow, made my main one dependent on that, and then added a "Source" Script Component to the new Data Flow... then I populated the new variable in that Script Component.

Thanks,

Chris

|||

tofferr wrote:

One small oddity... I wanted to set the variable in a Script Component for testing (rather than command line). However, I couldn't set the Script Component as the first object on my Data Flow since the Flat File Source object will not take a predecessor. Instead I created another Data Flow, made my main one dependent on that, and then added a "Source" Script Component to the new Data Flow... then I populated the new variable in that Script Component.

Thanks,

Chris

Why not use a Script Task immediately before your data flow task to set the variable?

|||

jwelch wrote:

Why not use a Script Task immediately before your data flow task to set the variable?

That's a good idea... but, how do I access the varibales? Variables.variableName is not getting recognized in the script component editor, even after I added it to the ReadWriteVariables list.

Thanks,

Chris

|||

This article from Jamies is right on point for much of this.

http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Chris

|||Dts.Variables("NameofVar") should work in the Script Task.|||

jwelch wrote:

Dts.Variables("NameofVar") should work in the Script Task.

You also need to remember to specify the (case-sensitive!) variable's name in the ReadWriteVariables property of the Script task for this to work.

|||

tofferr wrote:

Matthew,

Thank you! I knew that should be possible, but, I couldn't figure out how. Based on your hint (expression) I was able to find the expressions section on my Connection's properties pane. I added an expression the ConnectionString (path) to a variable. It works!

Thanks for the feedback!

If you ask me, SSIS Expressions are the most powerful single feature in the SSIS toolset. Being able to declaratively bind arbitrary expressions to arbitrary properties of arbitrary components (with a few restrictions, of course), so that the expression is evaluated whenever the property is accessed, is incredibly powerful and cool. So many people I talk to use SSIS without having a real "light bulb moment" about expressions, but to me they are a primary tool for making SSIS packages configurable, maintainable and reusable.

I feel a blog post coming on...

sql