Dynamically build connection objects for MS Access databases in SSIS
By: Hal Hayes | Updated: 2008-02-19 | Comments (13) | Related: More > Integration Services Connection Managers
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?
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
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):
|ResultSet||Full result set|
|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|
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:
|ConnectionString||"Data Source=" + @[User::mAccessLocation] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;"|
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.
- 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 Updated: 2008-02-19
About the author
View all my tips