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

x
?
Solved

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

Posted on 2010-09-01
11
Medium Priority
?
614 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 1800 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 1800 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 1800 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 

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 200 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

877 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