Dynamically build connection objects for MS Access databases in SSIS

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

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

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.

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:

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.

I just successfully ran the above concept on VS2012. I did have a few hickups, so I thought I would share with you how I resolved them. I have missed out some steps that Hal has already covered, so try and match it with his steps:

1. Create your Data Flow task first with a static connection to an access db.2. Create two variables one of object and another of string, these will store your access db location. One is a result set, while the other is just a string. I am using an object for the result set because I am also pulling multiple values including the location.3. Add your exec sql task editor and the foreach loop container as per Hal.3. Exec SQL Task Editor -> Set result set to full, hence the reason for the object variable. Set your connection details as per Hal. In the result set option, map the 0 to the object variable. 4. Foreach Loop Editor -> Collection as per Hal. In variable mapping set the string variable to index 0, this should match your SQL query in the exec sql task editor.5. Move your Data flow task into the loop container and set the DelayValidation proptery to true for the task.6. Set the "ConnectionString" & "ServerName" expressions for your access connection with the following "Data Source="+ @[User::Location] +";Provider=Microsoft.Jet.OLEDB.4.0;" & @[User::Location] respectively.7. Run.

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?

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:

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.

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:

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.

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.

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

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

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!