SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Christian Allaire
Christian Allaire
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 106
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98448 Visits: 13320
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).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Christian Allaire
Christian Allaire
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 106
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
Koen Verbeeck
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98448 Visits: 13320
You could try the following expression:

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




EDIT:
Forget about this suggestion, I forget that you were trying to call the subpackages from a master package. @[System:TongueackageName] 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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
JeriHatTrick
JeriHatTrick
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 215
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
badchippo
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 102
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
Surjit Madiwalar
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 169
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
learning4less
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 50
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
tmitchelar
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 508
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search