MSSQLTips

This post is related to this tip: Dynamically build connection objects for Microsoft Access databases in SQL Server Integration Services

http://www.mssqltips.com/tip.asp?tip=1437

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

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

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

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.

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.

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.

 

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