Solved

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

Posted on 2009-05-19
8
332 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:Jackfy
  • 5
  • 3
8 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24423612
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
 
LVL 1

Author Comment

by:Jackfy
ID: 24423674
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24424002
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:Jackfy
ID: 24424052
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
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 24424208
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24424214
helped?
now with ssis variable you can continue your work... correct?
Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
0
 
LVL 1

Author Closing Comment

by:Jackfy
ID: 31583059
Thanks. This is exactly what I'm looking for. I forgot to try the Script task.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24424328
Dear friend,
I'm very happy and we be here to help you again if you need!
Regards,
Pedro
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question