Solved

SSIS 2008 VB Script task:  How to detect folder that the .dtsx file is in?

Posted on 2012-03-21
5
466 Views
Last Modified: 2012-06-27
Hi guys

I'll be running an SSIS 2008 .dtsx file off of the file system.

Quesion:  In a script task, what's the code for getting the folder that the file is in?

I'm going to put some ini-like files there and want it to be used in the same folder as the .dtsx.

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
Wouldn't it be better/easier to make the location configurable through a package variable and package configuration?
That way you can put the files anywhere you like (provided the configuration is correct of course), plus you don't need to come up with the logic to find out where your package is located (not even sure if that's possible from inside the package).
Your script can then just use the package variable to pick up the files.
0
 
LVL 65

Author Comment

by:Jim Horn
Comment Utility
I have 15 connections, so the command-line necessary to feed 15 variables would be ugly.
They didn't like the XML solution either, although I'm leaning towards pursuading them to accept it as it avoids a lot of coding.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
Hi Jim,

Could you explain a little more what you're trying to achieve?  What I proposed should only come down to the creation of one variable that contains the path to the location where your INI-like files are stored, independent of your connections.

If you're trying to make your connections flexible, you should really check out package configurations.  If "they" don't like configuration through an XML file (not sure why though), perhaps a SQL Server table is another option?

Here's some info on best practices regarding package configs: to be able to run packages on different environments, with connection managers pointing to different servers and so on, store all properties (connection strings, variable values,  ...) that need configuration in a SQL Server table.  Your DEV environment would have its config table, your PRD environment would have another config table (with the same name).
The next step is to inform your package where the config table is located.  This is done through an additional connection manager that points to the DB containing that table.  Obviously this manager needs configuration too.  This one connection string can be configured by using either an environment variable or an XML file.
Also, when adding the configurations to your package, ensure the configuration for the config DB is the first one in the list (they are processed top-down).

Hope this helps a little?  Just let me know if you need further info!

MSDN page on package configs: http://msdn.microsoft.com/en-us/library/ms141682.aspx

Valentino.
0
 
LVL 65

Author Closing Comment

by:Jim Horn
Comment Utility
The client eventually went with the Package Configuration / XML file, as I was ultimately able to sell them on editing it if they had to.

Thanks for the follow-up.  Sorry for not replaying sooner.

Thanks.
Jim
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
No worries, glad to hear you got them convinced! :)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now