Solved

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

Posted on 2010-09-01
11
605 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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