Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What is the best way to make SSIS execute package task connections portable ? Expand / Collapse
Author
Message
Posted Friday, April 16, 2010 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:16 PM
Points: 6, Visits: 86
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

Post #905118
Posted Monday, April 19, 2010 2:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #905747
Posted Monday, April 19, 2010 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:16 PM
Points: 6, Visits: 86
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
Post #906133
Posted Tuesday, April 20, 2010 1:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
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.




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #906602
Posted Tuesday, April 20, 2010 8:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:18 PM
Points: 40, Visits: 187
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.
Post #906901
Posted Tuesday, July 13, 2010 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 9:11 AM
Points: 1, 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
Post #951644
Posted Wednesday, September 8, 2010 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 1:50 AM
Points: 174, Visits: 155
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



Post #982128
Posted Monday, April 4, 2011 11:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 8:20 AM
Points: 1, 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.
Post #1088180
Posted Monday, April 4, 2011 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
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.
Post #1088296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse