Solved

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

Posted on 2012-03-21
5
471 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
ID: 37751778
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
ID: 37752440
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
ID: 37752547
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
ID: 37853708
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
ID: 37854473
No worries, glad to hear you got them convinced! :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

786 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