次の方法で共有

SSIS Tip: Using a File Path Parameter for Multiple Flat Files

  • [アーティクル]

SSIS with SQL 2012 and SQL 2014 brought a lot of great enhancements to SSIS that ease deployment and reconfiguration of packages. The project deployment model, SSIS Catalog, and parameters make it a lot easier to manage SSIS . It also helps decouple environmental configurations from the SSIS code. This way DBAs don’t need to be modifying packages or worry about config files on the filesystem that may have passwords with plain text in them. It is awesome and as someone who managed hundreds of SSIS packages at a previous role, this is a big win!

Note: I am going to assume that you readers are at least slightly aware of project and package parameters and how they work. If not, that's ok, you will still get something out of this. You may just need to go do a little more research to better understand it.

Anyway, I have a package where I have 8 files I plan to load into 8 tables. This means that I have 8 flat file connection managers in my SSIS package. This is a quick and dirty package I am making for my internal team to use which means we will be taking these 8 files that we have generated and load them into a db on our local SQL instances to do some brief analysis. I want to parameterize the package so I can just hand over the dtsx file to my colleagues to reuse for themselves. This is not a traditional SSIS use case, but the problem is common….

I right click on the connection manager for one of the flat files and select "parameterize". I create a new parameter with the filepath and filename

clip_image001

This is great! Just a few clicks and I created a parameter. Now here is the problem…. I create 8 of these for all the flat file connection managers.

Problem: Now when I hand the package off to a team member, he/she will need to modify 8 parameters for their environment. Since I know that all 8 files will be in the same directory, that just seems silly.

Solution: First of all, do not right click on the connection manager and select "parameterize". Sounds silly but it will eventually be parameterized.

Create a parameter at the package or project level. For my use case, I am using package level for portability, but I highly recommend using the project deployment model and project level parameters. You can see my example here:

clip_image002

Now you go within each connection manager in the properties window and click on the ellipse button next to Expressions (highlighted).

clip_image003

You will then select "Connection String" as the property and click on the ellipse again to get into the expression builder.

For the expression, you drag and drop the InputFilePath parameter, then add the plus sign and the filename in quotes.

clip_image004

Then click on OK and notice you will now see the F(x) now with the connection manager icon. You see this same icon after you parameterize the connection manager via the GUI with right click "parameterize". This different method simply allows you to apply a parameter and an expression.

clip_image005

Now simply repeat this process for all other connection managers. The only difference in the expression will be the file name.

Whether you run the package in SSDT or configure the package in the SSIS Catalog, you just need to modify the file path in 1 place to alter all 8 of my connection managers. You can see this in SSMS:

clip_image006

So when you are creating SSIS packages, think about ways to minimize the number of configurations needed. This will help minimize configurations needed during deployment.

The drawback to this approach is that you are now assuming that all those files will always be in the same directory. Make sure you consider whether or not that is what you want to require within your process. Rarely is there a solution that is ideal for every use case, but depending on your needs, this can be another solution to help simplify your package parameters.

Comments

  • Anonymous
    June 18, 2015
    Very nice post!

  • Anonymous
    February 16, 2016
    Thanks for this idea! It really helped me out. My situation is one in which I am performing development on two separate machines in two different locations, so the path to my files varied only by the login username, as in C:users<userid>folderfile.txt. I parameterized my userid because I have different logins in the two different systems, and it worked perfectly!

  • Anonymous
    August 02, 2016
    The comment has been removed

    • Anonymous
      August 02, 2016
      Further research led to scripts to define the filepaths from a package/environment variable. That looked something like: Dts.Variables("RawFile_myFilePath").Value = System.Environment.GetEnvironmentVariable("HOMEDRIVE") + "\ssis\mySubFolder\myFile.raw";Creating the RawFile_my_FileName variable so the script could define it showed access to expressions:@[$Package::RawFileDrive] + "\ssis\mySubFolder\myFile.raw"(where RawFileDrive will be "C:" for local development and "D:" for server processing.)Lots of variables in the package, but only one parameter for the runtime environment.