How do I get the full file path info for the current executing package?

I have set of SSIS pacakges. I want to make my SSIS application more portable. When I move the SSIS files from one location to another, the connection string for the sub package can always be correctly populated based on the parent package's location.

For example I have package A.dtsx and B.dtsx, where A is the parent of B. If all the files are copied under C:\SSIS, when A kicks of B, the connection string should be C:\SSIS\B.dtsx. If the files are executed from C:\Program Files\SSIS, the connection string for B should be C:\Program Files\SSIS\B.dtsx.

The question is, regardless where A.dtsx is lcoated, how I can obtain the file location in SSIS when it's executed?

Thanks.
LVL 1
JackfyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PedroCGDConnect With a Mentor Commented:
Ok...:-)
check the attached package... Use a script task as the first component to execute to update the uvRootFolder
SSIS-Interface.JPG
SSIS-Interface2.JPG
SSIS-ScriptCode-UpdateRootFolder.JPG
Package-EE111-dtsx.txt
0
 
PedroCGDCommented:
Are you storing the packages in the file system?
You already tried to use SSIS variable that store file system folder to pass to the package B?
regards,
Pedro

www.pedrocgd.blogspot.com
www.BIResort.net
0
 
JackfyAuthor Commented:
Yes, I do store them in the file system. And I do have the SSIS variable "RootFolder" to store the root folder info. My question is, instead of using DTEXEC to overwrite it from the DOS commandt, how I can have the value of that variable automatically refreshed within SSIS, as I described in my original post?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
PedroCGDCommented:
Add this code to a bat file

set OLDDIR=%CD%
dtexec /F "%OLDDIR%\pckLoadData.dtsx

rename the package name from pckLoadData.dtsx to your package name and execute the BAT file
Helped?
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
0
 
JackfyAuthor Commented:
Hi PedroCGD, as mentioned in my earlier reply, I'm looking for the SSIS solution, "instead of using DTEXEC to overwrite it from the DOS command". I'm wondering if there's any system info I can grab from. I checked all the available system variable and this info isn't there.
0
 
PedroCGDCommented:
helped?
now with ssis variable you can continue your work... correct?
Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
0
 
JackfyAuthor Commented:
Thanks. This is exactly what I'm looking for. I forgot to try the Script task.
0
 
PedroCGDCommented:
Dear friend,
I'm very happy and we be here to help you again if you need!
Regards,
Pedro
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.