Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-21
5
Medium Priority
?
483 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
[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
  • 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 66

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 2000 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 66

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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