[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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