May 31, 2009 at 9:58 pm
Hi All,
I want to export all the tables in an MS Access database to SQL Server tables using SSIS. I've to create a single package for that.
My questions are how can we loop through all the tables in an Access database.
Can we achieve this by using Foreach loop container if so which property do I need to select.
Or is there any other way of achieving this.
Thanks for your help in advance.
June 1, 2009 at 12:40 am
Does it have to be SSIS? Have you thought about using the Access upsizing wizard?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 1, 2009 at 5:52 am
Hi Thanks for your reply. It has to be a SSIS package..We are taking each table and comparing with another table in the database. Since comparison cannot be done in Access wizard it has to be SSIS package.
Thanks in advance
June 25, 2009 at 7:06 am
I have the same question. Is my only option to first export the Access table to a .txt file then import the .txt file into my SQL table?
December 1, 2015 at 6:50 am
Hello,
Yes, you can achieve this easily using a Foreach Loop and an ADO.NET connection.
1) Drag a Foreach Loop on your Control Flow.
2) Click once on your Foreach Loop and create a new variable named TableName of the type String.
3) Edit your Foreach Loop and go to the Collection section.
4) Set the Enumerator to "Foreach ADO.NET Schema Rowset Enumerator".
Under Enumerator Configuration set:
5) the connection to the ADO.Net Connection which points to your .accdb-file. (or create a new one by choosing "New Connection" from the list)
6) Select "Tables" as Schema
Under Variable Mappings:
7) Choose the variable "User::TableName" and assign index 2
For each iteration through the loop, the variable TableName will contain the name of a table, which you can use within the scope of the Foreach Loop.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply