SSIS connection parameter based on parameter/variable

0
votes

I am building an SSIS project to import ~40 CSV files. I am creating one package for each import and a master package to run them all.

The CSV files all live in the same file location, but that location may change. What I would like to do is set a project-level parameter for the file location and construct the connection string for each package's connection by concatenating that project-level parameter with the file name.

Is this possible?

1
ssisssis-2012
I don't see why not. Have you tried? Prior to project-level parameters, this could have been handled with a config file. - Tab Alleman
Yes I have tried. I do not see a way to derive a variable from another. - JHFB
Concatenation usually works. Set the value of variable B to "Some Text" + VariableA + "Some more text". - Tab Alleman
If you could try this in SSRS and tell me precisely where to do it, that would be great - because the UI does not appear to offer this option. I agree this "should" work and "usually" works but I cannot get it to work. - JHFB

1 Answers

1
votes

You concatenate variables in SSIS with Expressions. To get to the screenshot below, I created VarA, and gave it a simple string value.

Then I created VarB, made it a string type, and clicked the ellipses in the Expression box:

Screenshot

You can see in the image how I then concatenated the first variable with some text to make the second variable.

So to apply to your question, use the Package-level parameter (file location) as VarA, and in each package, use this technique to build VarB with the file name for that package.

My example is actually backwards in relation to your goal since I put the file name in the first variable. But it demonstrates how to concatenate string variables.