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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » What is the best way to make SSIS execute...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Christian Allaire

Posted Friday, April 16, 2010 12:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:02 AM
Points: 6, Visits: 74

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

Koen Verbeeck

Posted Monday, April 19, 2010 2:01 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364, Visits: 6,462

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

Christian Allaire

Posted Monday, April 19, 2010 10:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:02 AM
Points: 6, Visits: 74

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

Koen Verbeeck

Posted Tuesday, April 20, 2010 1:57 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364, Visits: 6,462

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

JeriHatTrick

Posted Tuesday, April 20, 2010 8:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 8:35 AM
Points: 40, Visits: 175

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

badchippo

Posted Tuesday, July 13, 2010 10:22 AM

Forum 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

Surjit Madiwalar

Posted Wednesday, September 08, 2010 3:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 2:12 AM
Points: 169, Visits: 128

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

learning4less

Posted Monday, April 04, 2011 11:08 AM

Forum 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

tmitchelar

Posted Monday, April 04, 2011 2:56 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:25 AM
Points: 121, Visits: 414

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 »

Permissions