Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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

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
Jim Horn
Asked:
Jim Horn
  • 3
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
No worries, glad to hear you got them convinced! :)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now