Solved

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

Posted on 2009-05-19
8
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

636 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