bar0822
asked on
Import tables from multiple mdb into SQL using SSIS 2005
What is the best way to import tables (Total 7) from Access databases into one SQL table. I am using SSIS and created a connection to each of the databases. Not all of the tables have the same columns. Each import has its own container - with sql tasks and aggregations. Problems with the data types converting - so instead importing into temp table with char datatypes - then inserting the data into the sql table. Is there a better way to do this in SSIS? Also, need help with counting the records imported and date of import in ssis and exporting to Excel. thx bb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you say " Problems with the data types converting - so instead importing into temp table with char datatypes - then inserting the data into the sql table" - are you putting a data conversion transformation from your access connection/transformations
ASKER
This is not a one time import - scheduled to run monthly. I tried the data transformation task and changed the type to DT_STR with the same length as the sql table and kept getting the same error message: cannot change unicode to non unicode. I was pulling my hair so I just gave up and went with the put data in temp table with char datatype fields and then into the sql table - this worked with the first table. I am starting on importing my second table that has some of the fields as the first import. I am looking to use the same package with a second data task flow in a second container - after the first import/container has finished, then start the second but I must do this for 7 imports - would it be more efficient / faster if I created separate packages for each and called them all from a main package?
regards,
regards,
No,
Best way is to use for each loop and get each *.mdb and insert data to table using dataflow task.
Best way is to use for each loop and get each *.mdb and insert data to table using dataflow task.
ASKER
this was my first solution as described in my question but was looking for another way to do it. I tried the data conversion process but there were too many errors and could not spend too much time on this. So I went with my solution which was the same as Genius.
I appreciate the responses from all and they were all good.
I appreciate the responses from all and they were all good.
Is is "one time import" ?
I mean do you want to do this process just one time? or you need it later?
if this is one time job, you can use Import/Export wizard to import these data into sql server staging tables, and then you can query on these data and get result as you want .
But If you want to write a package to use it on timely basis, you need to create 7 source/transformation/dest