Solved

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

Posted on 2010-09-01
11
602 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 38
SSRS 2013 - Overlapping reports 2 22
hyperlink data type in SQL 3 27
How to use three values with DATEDIFF 3 24
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

773 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