May 30, 2008 at 3:33 pm
Hello Everyone,
New project - just started working on it today. Once again, we have our folder of Access 2000 databases which are all alike but are for different clients. We need to loop through them to run a sql statement that looks for duplicate bill id's. Ordinarily, this would be totally easy enough and I have done this before by looping through the folder, getting the file names and storing them in a variable, then loop through the object variable to build a connection to each file. Today, however, we have a new kink.
Some of the Access databases have a password and some do not.
Good news: I have a complete list of all database names and their passwords in an Excel table.
So, my approach was to build the connection string for the access databases.
Step 1. Use Execute SQL to pull Excel table into a sql server database.
Step 1.a. Use derive transform to build connection string into a new column. Here is an example connection string:
Datasource=\\servername\sharename\databasename.glm;;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=stupidpassword;
At this point, we now have our temp table with the names of the databases and a complete connection string.
Step 2. Use Execute SQL to load connectionstring dataset into a variable of datatype object.
SELECT ConnectionString FROM DatabaseListTable
Step 3. Loop through object variable. Set the Connection String expression in my Access database connecter to look at the variable @[User::conString]
Unfortunately, the package errors out with:
Failed to acquire connection "Access Database". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Get Count of Duplicates
The variable in the locals window has the correct connection string.
What do you guys think?
JamesNT
May 31, 2008 at 2:38 pm
My apologies, everyone.
The connection string should start with Data Source, not Datasource.
James
June 1, 2008 at 11:18 pm
Hey James,
Does this mean that your problem was solved?
Actually quite nicely done, using a for each ADO loop and setting the connectionstring dynamically from there.
Had a similar situation where my files came in as DDMMYY. Using a for each file enumerator would have sorted the files wrong (310108 would have been sorted AFTER 010208). Used a for each file enumerator to load the filenames to a SQL destination, and used a funky SQL statement to order the files correctly, passing the ordered resultset into a for each ADO enumerator.
This solved my problem, and set my connectionstring in the order I required.
~PD
June 2, 2008 at 9:05 am
pduplessis,
Thank you for the compliment. Yes, that did solve my problem. I appreciate the follow-up!
Also, I find your story intriquing as well. Sometimes you just have to get creative with this stuff.
Have a good one!
JamesNT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply