?
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
?
616 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

592 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