Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Dynamically build connection objects for MS Access databases in SSIS

MSSQLTips author Hal Hayes By:   |   Read Comments (11)   |   Related Tips: More > Integration Services Connection Managers

Problem
As a portion of our daily data upload process, we receive data in the form of Microsoft Access files (*.mdb) which needs to get uploaded to a SQL Server 2005 database.  We want to build a SQL Server 2005 Integration Services package that loads all our Microsoft Access files and uploads data based on parameters in a table in our database.  We want to make this an automated process, but the number of Access files may change over time.  Do you have any solutions on how to build the SSIS package such that we do not have to modify our SSIS package for each new file?

Solution
In this example, let us assume that our sales force sends their sales order information as an Access file and each each sales team has their file stored in a different folder. All available Access sales files need to be loaded in the same process to keep the sales information current. The company is continually adding new sales teams, but not all sales teams will submit a file, so we want to be able to indicate the files (by location) that we want processed dynamically when the SQL Server 2005 Integration Services (SSIS) Package is executed.

The first step is to build a listing of the Microsoft Access files that need to be processed. For this particular solution, the active Access file locations will be stored in a table in the SQL Server database. Here is the simple create table script to hold the file information.

Sample Access Table Definition

CREATE TABLE [dbo].[AccessDataFiles](
      
[ID] [int] IDENTITY(1,1) NOT NULL,
      
[FileLocation] [varchar](500) NOT NULL,
      
[Updated] [datetime] NOT NULL CONSTRAINT [DF_AccessDataFiles_Updated]  DEFAULT (GETDATE())
ON [PRIMARY]

When populated, the table will look something like below.


SQL Server Integration Services Package

For the Integration Services Package, our solution include the following steps:

1. Execute a SQL query to select records containing the file locations.
2. Iteration over the records returned, creating a connection string for each Access file.
3. Set the variables that are assigned to the OLE DB connection object for each the Access file.
4. Execution of the data flow operation to extract data from an Access table into a SQL Server table.

To implement this solution, create an SSIS project in the SQL Server 2005 Business Intelligence Development Studio. Create a connection to one of the Access files (call it AccessOrderMDB). Next, create an OLE DB connection to the SQL Server database (call it AccessUploadDB). With our connections in place, we are ready to build the package.

Step 1. Retrieving the file locations

During this step, we will use SSIS to create a record set from a SQL statement that will be used during the looping step.

Add a variable called "mAccessFiles" at the package level with the data type of Object.

Add an Execute SQL Task to the Control Flow surface. Set the following properties in the General tab (double click on the object to launch the Execute SQL Task Editor):

Parameter Value
ResultSet Full result set
Connection AccessUploadDB
SQLSourceType Direct input
SQLStatement Select FileLocation From dbo.AccessDataFiles

Select the "Result Set" tab on the Execute SQL Task Editor and set the following properties:

Result Name Variable Name
0 User::mAccessFiles

Step 2. Configure the ForEach loop

Add a Foreach task to the Control Flow design surface. Within the Foreach task add a Data Flow task. Your solution should look like the following:
 


 

In the Foreach Loop task, select the Foreach Loop Editor (double click on the task or select "Edit" from the context menu).

Select the Collection tab, and in the Enumerator property, set the drop down value to "Foreach ADO Enumerator". In the Enumeration configuration settings, under ADO object source variable, select the variable we previously set in the proceeding step, User::mAccessFiles. Also, select "Rows in the first table". This makes it possible to iterate over the returned rows stored in our variable as an ADO object.

In the variable mappings map the variable as follows:


Step 3. Dynamically configure the Access connection object through Expressions

To successfully connect to an Access file both the connection string and ServerName values must be set correctly. When you use the connection wizard, these values are properly populated. Unfortunately, when you want to dynamically configure your connection to the Access files, you must set both. Note the Properties values below.

To set these values dynamically the properties should be changed as follows:

Property Value
ConnectionString "Data Source=" + @[User::mAccessLocation] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;"
ServerName @[User::mAccessLocation]

Step 4. Set Dataflow Properties

Now we simply configure our data flow using our OLE DB Source object from our Access table to pump data into our SQL Server database OLE DB Destination table. The OLE DB connection manager should point to our Access connection object. Since these steps are typical data flow operations, the details will be left to the reader.

 

Next Steps

  • Configuring your SSIS package to support dynamic connections to multiple Access databases can be achieved, but it is important to remember that you need to set two properties to successfully connect to the Access files, the ConnectionString and the ServerName.


Last Update: 2/19/2008


About the author
MSSQLTips author Hal Hayes
Hal Hayes is a managing Consultant at Excella Consulting.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, February 08, 2010 - 1:14:51 PM - jiml Read The Tip

I've been trying to implement this.

After entering the dynamic expressions in the expression editor my Source Data Flow breaks.   Do you have to modify the Source Data Flow from the parameters initially created when the Access Data connection was pointing to one Access source file?

 


Tuesday, November 24, 2009 - 4:23:02 PM - dwango121 Read The Tip

 Hello everyone,

Thanks for the great tutorial but I was wondering if someone could lend a hand in helping me resolve this error that I am getting on my oledb connection manager. I have developed a SSIS package and part of this package is to loop through a directory of access 2007 databases and import them into a sql server staging table.

This works a treat for a single access database but now after I have set 2 expressions up for the connectionstring and servername properties the Server or filename option in the oledb connection manager just resets itself back to 0 all the time. Even when I retype a valid path to the database and complete the test connection it just resets itself back to 0 again

Also when you click on the oledb source edition it comes up with the following error:

Could not find file 'C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\0'.

My 2 expressions are:

Connection string:  "Data Source=" + @[User::SourceFullfilename]+ ";Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Password=;"

Servername:  @[User::SourceFullfilename]

These both seem to parse ok. I thought it was because the  @[User::SourceFullfilename variable was empty at runtime but the connection manager will not let me type a valid path the databases in. 

Thanks in advance for any help you can give me!!

Alan

 

 

 


Friday, April 17, 2009 - 3:31:12 AM - viDo Read The Tip
hey..i found suitable article for this problem :
http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

i've tried it and works perfectly. i hope so for you ;)

Thursday, February 19, 2009 - 12:05:58 PM - Eugene Read The Tip

What data type should be for selected for the variable mAccessLocation in the exercise http://www.mssqltips.com/tip.asp?tip=1437 ?

 We should map data type Object (variable mAccessFiles)

to the mAccessLocation, which should be string, because we need to insert

 @user:: mAccessLocation in the Connection string for the Connection Manager.

I follow the procedure and all process does not work for me. I think the variable mAccessLocation can not take mAccessFiles as an object.

Could you please clarify for me?

 

Thank you in advance.

Eugene

 


Thursday, August 28, 2008 - 11:53:04 AM - halhayes Read The Tip

Make sure you also set the ServerName property with your variable @[User::varSalonPath] . I believe that might be causing your error. Access requires both ConnectionString and ServerName properties to be filled.

--

Hal


Wednesday, August 27, 2008 - 3:27:09 AM - Mitchies Read The Tip

Hi Hal

Thanks for that article.

I have followed your directions meticulously but have obviously left something else.

The problem occurs when I change the Access data source from being a static datasource to being dynamic based on variables passed as discussed in your article. As soon as I change the data source to dynamic, I get the following error when running the package:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Salons Access" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Please note that the Expressions: ConnectionString is set to the following value:

"Data Source=" + @[User::varSalonPath] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=********;" 

I would very much appreciate any help you can offer.

Kind Regards

M.

 


Thursday, March 06, 2008 - 5:25:18 AM - halhayes Read The Tip

This is a general hint that may assist you with your project. 

For dynamic SQL in your data flow, you might want to look at using an OLE DB source and set the property "Data Access Mode" to SQL command from variable. You can create a package variable that you dynamically modify before execution reaches the data flow task.

 Hint: In setting up your SSIS package, metadata is vitally important. Therefore, prepopulate your variable with a sql statement that matches output values you will use in your data flow. For example, if you are dynamically selecting from the Customers table (SELECT ID, FIRSTNAME, LASTNAME FROM CUSTOMERS) and modifying the Where clause on the fly, then place a select statement text for the same table in the variable you are using before you set up your dataflow in the SSIS designer.


Tuesday, March 04, 2008 - 12:12:41 PM - extremelogic Read The Tip

I am trying to create an Export app for my web application utilizing SSIS and for that I have designed an xml export feed which will be submitted from the web application to an MSMQ. This xml feed has SQLStatement, ExportFormat, UserName and UserEmail nodes.

SSIS will get the xml feed from the message queue and parse it to populate its package variables. This is accomplished via a script task and is working as expected.

This XML feed has a SQLStatement node which has the sql statement that SSIS has to execute and export the result set data in an excel file to a specific location. I was trying to use DataFlow task after I populated the package variables but then reallized that it requires a predetermined table/view or a sql statement for it to build mapping. You cannot do an on the fly sql thing.

 Do you have some ideas as how to make dataflow task run sql statements on the fly and dump the data to an excel file OR is there another way to dump the data of a sql statement fed externally into an excel, access or csv files in SSIS.


Wednesday, February 20, 2008 - 7:19:25 AM - carlm Read The Tip
Thanks for the tips! I got it... finally...it was where I was building my connection string...I had tried to do the "Data Source="... right in the ConnectionString properties area...I kept getting errors about an improper ConnectionString... I built in in the Expression builder mapping the "Data Source="... to the ConnectionString and then voila! it all worked perfectly. I didn't put anything in the ConnectionString order ServerName properties area at all..only in the Expression builder. Thanks for your help! cdm

Tuesday, February 19, 2008 - 8:40:51 PM - halhayes Read The Tip

Sorry about that. There was a lot to cover in this particular topic.

Back to the problem at hand. The most practical way to start a package with a dynamic connection, particularly for establishing a workable Data Flow, is to start from a static connection. For this case, I would use an Access file that is representative of the files that you will be connecting to dynamically. When you start building the package, build that connection first.

Next step is to build the Data Flow. By working from a static Access database file, you can set up the data flow process (the metadata), and ensure that it works the way you want it to. So, you will have an Access connection object in your connection manager window. This is the position to start from. In fact, the best way to build a package like this is to start with a working Data Flow first and work your way out!

Once you have a working data flow, the next steps are to set up the variables, Execute SQL tasks, and the ForEach operation. When you have these in place and know they work the way you want them to, then you can set up your dynamic connections. As was explained in the article, you would set the Expression property (using the Expression Editor) that substitutes the variable value, with its dynamic values to the location of the Access database, for the two properties of the Access connection (ConnectionString and ServerName).

Hope that helps.

- Hal


Tuesday, February 19, 2008 - 2:34:27 PM - carlm Read The Tip

It is ironic that the problem I'm having was considered too trivial to cover and the stuff I have working right was discussed in detail. :)

I've been able to set up a dynamic Connection Manager to gather MsAccess data tables inside a ForEach loop...my problem lies in the Data Flow task itself. Inside the dataflow task, I try to set point my OLE DB Source to my dynamic connection manager but now I can't select any tables, etc. since there is no hard-coded database available in that Connection Manager. I've been able to thoroughly test my whole package but I cannot find a way to cycle through MsAccess databases. My DataFlow never gets off the ground. Newbie issue I'm sure...any ideas? I've gone through your example and at the end you imply that the rest is trivial...exactly what I need to see!

Thanks! cdm




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.