Solved

Import tables from multiple mdb into SQL using SSIS 2005

Posted on 2011-02-26
6
777 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:bar0822
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34987206
>> 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?

Best way is to populate records into temp table and then insert it into actual table..
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34988128
If you looks for an automated way for all tables with different structures, you can't use SSIS Data Flow task, because it doesn't support dynamic meta data.
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/destination. (because of different data structures)
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34989514
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 to your sql table? If not you should try putting a data conversion transformation before you draw the arrow to the sql connection to change the columns out of unicode. And then map the new converted columns into your table. control flow data conversion transformation
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:bar0822
ID: 35003381
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,
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35239552
No,

Best way is to use for each loop and get each *.mdb and insert data to table using dataflow task.
0
 

Author Closing Comment

by:bar0822
ID: 35432951
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.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now