What is the best way to make SSIS execute package task connections portable ?

Question

What is the best way to make SSIS execute package task connections portable ?

Christian Allaire

Mr or Mrs. 500

Points: 558
More actions
April 16, 2010 at 12:35 pm

#220613

Hello,
I have a main package which makes call to sub-packages using the Execute Package Task. My packages are stored on disk (.dtsx files) so the task uses a File Connection. What is the best way to make these file connections portable ?
I know could create a different configuration for each package, but I there must a more elegant solution ...
Thanks,
C. Allaire

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You could use a configuration table and store your connectionstrings there. That way, every connection is in the same place and easy to view.

But, if you have dozens of packages, I would recommend storing the packages in the MSDB folder on SQL Server. That way you can use relative paths and you even don't have to change them when changing environments. (except for the connectionstring to MSDB of course).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Christian Allaire Mr or Mrs. 500 Points: 558 More actions · Answer 2

Thank you for your reply. The MSDB solution sounds like the best option.

I have tried the following solution with packages on the file system:

I use a global variable (vRootFolder) containing the path to the folder containing the packages and include this variable in a general configuration shared by all packages. Then I go in the connection's properties and use the expression builder to dynamically build the conn. string. with something like @[User::vRootFolder] + "\\MySubPackage.dtsx". It works well but I would like to use replace the "\\MySubPackage.dtsx" litteral with something dynamic like the connection name property. Any ideas ?

Chris

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

You could try the following expression:

@[User::vRootFolder] + "\\" + @[System::PackageName] + ".dtsx"

EDIT:

Forget about this suggestion, I forget that you were trying to call the subpackages from a master package. @[System::PackageName] will return the name of the master package.

I have no idea how to make that dynamic without some sort of hard coding. Or you type it in an expression, or you select the package in the editor window of the Execute Package Task. I do the latter approach, but I'm pretty sure my packages won't change names, so it doesn't really matter.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

JeriHatTrick SSC Enthusiast Points: 182 More actions · Answer 4

Store the contents of the child packages in a table, say ETLChildPackages. Your master package would have a step that read the contents of this table (child package names) and stores the values in an SSIS variable of type object--you'll use the result as a table basically. Then you place your Execute Package Task inside of Foreach Loop Container. From here you're going to loop through the results of your variable of type object. Let's call this variable v_Packages.

In order to accomplish this you'll use the Foreach ADO Enumerator option in the Foreach Task. In the Variable Mapping section of the Foreach task you would map a variable of type string, say v_PackageName, to the value in each row of your v_Packages, assuming you're only storing one "column" in v_Packages. If you are storing only one "column" in v_Packages then you'll map v_PackageName to Index 0.

Where ever this package moves to you just make sure the packages are stored in MSDB and the ETLChildPackages table is created and populated as necessary.

badchippo Valued Member Points: 71 More actions · Answer 5

Name the Package Task the same as the child package you are trying to run and use:

@[User::Exec_Path]+"\\"+ @[System::TaskName]+".dtsx"

Example: child package is "BlahBlah.dtsx"

Name the package task in your master package that kicks it off as

"BlahBlah". Call the task name instead of the package name in the expression (as above).

Viola

Surjit Madiwalar Mr or Mrs. 500 Points: 553 More actions · Answer 6

hi,

When i try this @[User::Exec_Path]+"\\"+ @[System::TaskName]+".dtsx"

i get below error message

------------------------------

The variable "System::TaskName" was not found in the Variables collection. The variable might not exist in the correct scope.

Attempt to parse the expression "@[User::gv_DtsxFiles_Root]+"\\"+ @[System::TaskName]+".dtsx"" failed and returned error code 0xC00470A6. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

(Microsoft.DataTransformationServices.Controls)

--------------------------------

thanks,

suresh

learning4less SSC Journeyman Points: 99 More actions · Answer 7

Hi Chris, I have similar problem. But you have not explained where did you enter connection string. Because I tried but does not work. I just want to use expression builder to use physical file system connection (Folder variable + file name scenario). Any help will be appreciated.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 8

Would recommend using an environment variable in your package configurations as well if you plan to transport packages between servers. Prod to Dev and Dev to Prod for example.