Solved

How can I use a variable in the ssis connection manager to change the Access Database?

Posted on 2010-09-01
11
601 Views
Last Modified: 2013-11-10
I am using an Access Database.

Each month I will use a different Access Database. How can I use a variable in the ssis connection manager to change the Access Database?
0
Comment
Question by:Mr_Shaw
  • 6
  • 4
11 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 450 total points
ID: 33576091
What you'll need to do is set up a user variable to hold the connection string to the access database.  And you'll also set up an Access Connection.

Now, under "Connection Managers" (usually at the bottom), click on your Access connection.  Within its properties, you'll see a section called "Expressions."  Expand the plus sign.  Then set the "ConnectionString" property to the variable you created earlier.  

So if your variable was "connectionstrings", you would set the Expression for "ConnectionString" to the following:
@[User::connectionstrings]

You'll need a way to populate the variable dynamically.  
0
 
LVL 16

Assisted Solution

by:carsRST
carsRST earned 450 total points
ID: 33576110
See image below as a sample of what you'll set in the "Expressions" area within the properties of your Access connection.
1443-PropertyExpression.jpg
0
 
LVL 16

Assisted Solution

by:carsRST
carsRST earned 450 total points
ID: 33576128
See blue highlight in this image.  Shows you where the "Expressions" area is located within a connection.
Expressions-Connection-Propertie.png
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33576259
To auto populate your variable, I would suggest holding the paths in another static database.  You can query for current month.

To literally populate your variable, I would suggest using the "Execute SQL Task".  See steps below to pull field from your database and populate your variable.

Add an "Execute SQL Task", double click it, and follow these steps:
1.  Set connection and SQL that pulls the one field holding your database path
2.  Under the Execute SQL task's properties, look for a section called "Result Set" - to which you'll set to "Single Row."
3.  Next, within the box options on the left, you'll select "Result Set"
4.  On the right, you'll set 0 for "Result Name" and your newly created variable for "Variable Name" - this will take the field you selected and populate your variable.



0
 

Author Comment

by:Mr_Shaw
ID: 33576311
2 secs i give it a try. thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Mr_Shaw
ID: 33576929
If i set my variable to the connection string property I have to use the data source string. For example,

"Data Source=Y:\SLA\Models\Models\ PCT - R2 - Month 3 - 2010.mdb;Provider=Microsoft.Jet.OLEDB.4.0;"

Is there a way in which i can just set the variable to the Access DB file name "Y:\SLA\Models\Models\ PCT - R2 - Month 3 - 2010"
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33577035
Yes, but you would probably need to do it in a script.

The link below has a couple of good functions for reading and writing to variables from within SSIS scripting.
http://www.developerdotstar.com/community/node/512

Your other option is to populate the full connection string from where ever you're pulling.  Just make the path the dynamic part.

0
 
LVL 16

Expert Comment

by:carsRST
ID: 33577053
Actually, i take that back.  You could concatenate a few variables to build one final variable.
0
 

Author Comment

by:Mr_Shaw
ID: 33577465
2 sesc, I'll have a read.
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 50 total points
ID: 33577506
Can you give a sample of your database name along with your complete filename..what part of thename would change?

If suppose, your database name is based on month and year then you can use.something like this

"Y:\\SLA\\Models\\Models\\PCT - R2 - Month "+(DT_STR,2,1252)Month(GetDate()) + " - " + Year(GetDate())

the above expression will yield to
Y:\SLA\Models\Models\ PCT - R2 - Month 9 - 2010
0
 

Author Closing Comment

by:Mr_Shaw
ID: 33578588
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

930 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