April 14, 2008 at 10:39 am
Hello, Everyone. I'm a long time reader of this site but I do believe this is my first ever post. So, please be gentle. 🙂
We have 70 + Access 2000 databases in a share on a remote server (\\servername\sharename\appfolder\data\*.mdb).
All Access 2000 databases are exactly alike. Each one has a patient table, a bills table, transactions table, etc. All tables have the same table schema. For example, all patient tables have ID, firstname, lastname, middle, birthdate, and so on.
I would like to loop through all databases and pull the information out of their tables and into a database in SQL Server 2005 that has the same tables. So all information in all patient tables in the Access databases will go into a single patient table in the SQL Server database.
Problem - As I'm sure everyone here by now has figured out, the individual primary keys in the tables will become useless and that is a problem. For example, we all know that a patient in Access DB 1 will have an ID of 000001 as will a patient in Access DB 2.
Proposed solution and steps I have working thus far.
I loop through all the *.mdb files in the data folder. I have a table in the SQL Server database call AccessList in which I have a primary key of byte datatype (works like Access's autonum). The names of the files, their path are entered into that table. When this loop is completed, you have a table that looks like this:
NUM Name Path
1 DB1 \\servername\sharename\appfolder\data\DB1.MDB
2 DB2 \\servername\sharename\appfolder\data\DB2.MDB
3 DB3 \\servername\sharename\appfolder\data\DB3.MDB
In order to salvage the primary keys, I thought it would be cool to enter the NUM from the AccessList table to, for example, a new column in the Patient Table to create a composite Primary Key. So the Patient table in the SQL Server database would look like this:
NUM PatientID Firstname Lastname Birthdate
1 0000001 John Doe 00/00/0000
1 0000005 Jane Smith 99/99/9999
2 0000001 Allen Someone 11/11/1111
3 0000005 George Clueless 55/55/5555
So I have the loop working to pull in the names of the databases as mentioned. I also have an Excute SQL Statement that will place the values of the AccessList into an Object Datatype variable so I can loop through the the entries in that table to open up each individual access file so I can execute dataflow. Making a dataflow to pull data in from the tables of the Access databases to the respective table in the SQL Server database is easy enough.
But how do I include the NUM from the loop as the NUM to the destination table so I can salvage my primary key problem?
JamesNT
April 14, 2008 at 11:10 am
I guess because I don't like multiple loops, I would have done this operation with only one loop container.
I would use a ForEach file loop container to loop through all of the access databases.
In the loop container, I would use an ExecuteSQL task to insert the location of the database I was currently working on.
I would have this return the @@scope_identity value into a variable.
Then, I would have a sequence container with all of the data flows for copying data.
In each of the data flows, there would be an OLEDB source connecting to the current access database, a derived column adding my variable as a column to the dataset, and an OLEDB destination putting the data into the SQL table.
April 18, 2008 at 9:41 am
Mr. Earl,
First, thank you for your response. Apologies for not responding sooner but things have been hectic around here. I'm trying to get ready for a conference/vacation starting next week and it appears everyone has waited until now to make me aware of certain issues.
I have two loops because I need the table that lists the GLM files. First, it allows me to delete any of those databases if I need to by just have an Execute SQL Task that I can modify easily. Second, there is another operation that needs that table because it needs the same list. The other operating isn't SSIS related. In other words, I'm knocking out a few birds with one stone.
I shall give your suggestion a chance as soon as I can - probably today - so I should have some feedback soon.
Thanks again,
JamesNT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply