Lets assume for starters you have a bunch of MDB files, all the same structure, but different filenames. You want to use the same SSIS package to loop through and import each file into the database. Now we cover 5 steps to create a dynamic MDB file connection string inside your SSIS package.
Step 1 : Create your Data flow, stitch up your basic data flow between your OLEDB Data Source (this will prompt you to create a Microsoft Jet 4.0 OLEDB Connection Manager where you will specify the location of your MDB file), and create your preferred destination. Test to make sure this is working before making the MDB source file dynamic.
Step 2 : Create a variable called filename and set it to data type String. Either manually populate the value for variable “filename” (e.g. D:\Folder\Somefile.MDB) or populate this variable automatically using the For Each File Loop Enumerator.
Step 3 : Create a variable called mdbconnection and set it to data type String. Use the following expression to form a valid MDB connection string using your filename.
(DT_WSTR,12)"Data Source=" + (DT_WSTR,100)@[User::filename] + (DT_WSTR,34)";Provider=Microsoft.Jet.OLEDB.4.0;"
Step 4 : Go to the Properties of your OLEDB Connection manager of type “Native OLE DB\Microsoft Jet 4.0 OLE DB Provider”, set the Delay Validation Property to True, then expand Expressions, and create an expression for the “ConnectionString” property, setting this to
Step 5 : If the tablename within each MDB file stays the same your are in business otherwise, you will need to additional dynamically handle the OpenRowset property of the Source connection inside the Data Flow task.
Step 6 : Crack open a can of Michelob – job done !