How to import MS Access data into SQL Server using SSIS?

Question

I spent most of the day looking around to help me something, but SSIS is such a huge tool that is still not useful, or maybe I just don’t understand it.

I need to take an old access db that has some problems and move it to the SQL server. I already have the schema configured in my SQL db. Old data must be corrected, for example, normalization and deletion of duplicates. My boss insists on using SSIS for this job, because we need someone here who knows how to use it, and currently almost no one does (one manager used it a long time ago).

So, I have a project created in BDIS, or visual studio, or something like this application. I created a connection manager for my db access, and I was able to drag the OLE DB source into the constructor and configure it to connect to this db. I also realized that I can select data from there.

Now what? I would rather be able to simply write a giant SQL script to capture all the data I need from Access db, convert it as I want, and pop it into the SQL server database. But it looks like I will need to use all these funky conversion tools in the designer. I also can not figure out how to get the received data on the SQL server. I have a connection in the Server Explorer panel, but everyone online says they never use the SQL Server destination. So I'm lost too.

My boss said Books Online help files would be very helpful. Until now, it was like finding a needle in a haystack submerged under a swamp, with three locks sitting on it. There is too much information, and none of this seems useful to me.

Edit

We hope that more information will be helpful. I think the wizards that come with SSIS are not strong enough for what I want, so if you have them, you will have to explain it. Here an example of what I should do, besides my reality, is a lot more tables with a lot of transformations.

Let's say I have a source table that looks like this:

 Companies ==================================================== | Name | Address | WidgetOne | WidgetTwo | |--------------------------------------------------| | ACME | 123 etc. | Trampoline | Cannon | ==================================================== 

I need to normalize this to two tables. And, obviously, you'll have to keep track of identifiers so that the widgets are associated with the right company. If you could help with an example of how this case will be handled using SSIS , from Access to the SQL server, then I will probably take it from there. Thanks!

Answer 1

The following example can probably give you an idea of ​​migrating data from MS Access to SQL Server. This example uses MS Access 2010 and SQL Server 2008 R2 . The package was written in SSIS 2008 R2 . Unlike the previously provided solution, this answer does not use the SQL Server Import and Export Wizard, and the package was created from scratch.

Step by step:

• Suppose the Access table is shown in screenshot # 1 with a table named Companies , containing two rows in an unnormalized way.

• And also assuming that the table structure in SQL Server is shown in screenshots # 2 and # 3 with two tables named dbo.CompanyInfo and dbo.WidgetInfo . Table scripting is provided in the SQL Scripts section. The tables are empty, as shown in screenshot # 4 .

• Create a new SSIS package. In the SSIS package, right-click the connection manager and select New OLE DB Connection, as shown in screenshot < 5 . In the "Configure OLE DB Connection Manager" menu, click the "Create ..." button, as shown in screenshot < 6 .

• In Connection Manager, select Native OLE DB/Microsoft Jet 4.0 OLE DB Provider and specify the path to the access file. In this example, I have a file in C:\temp\Source.mdb . Click OK. See screenshot # 7 . In the "Configure OLE DB Connection Manager" menu, click "OK" as shown in screenshot < 8 . Change the name of the connection manager to AccessDB (the name can be any of your preferences). See screenshot # 9 .

• Right-click the connection manager again and select New OLE DB Connection, as shown in screenshot < 10 . This time we will create a connection string for SQL Server. Select "Native OLE DB \ SQL Server Native Client 10.0" and specify the name and server name as shown in screenshot < 11 . Rename the connection manager to SQLServer (again, this is your choice). See screenshot # 12 .

• On the Flow Control tab of the SSIS package, place the Data Flow task and name it Populate CompanyInfo . Double-click the data stream task to go to the Data Stream tab. In the Data Flow Task, put OLE DB Source , a Derived Transformation and OLE DB Destination , as shown in screenshot # 13 . NOTE: You need to configure the tasks one by one in the order shown. Do not set all tasks at the same time and try to combine them.

• Configure the OLE DB source to read the Access database table, as shown in screenshots # 14 and # 15 . Configure the derived conversion to convert the text of the string from the Access database to Unicode, as shown in screenshot 16 . Configure the OLE DB assignment to insert data into the SQL table, as shown in screenshots # 17 and # 18 .

• Return to the Flow Control tab and place another data flow task, as shown in screenshot # 19 .

• In the second data flow task, we read the same company table in the Access database and try to populate the WidgetInfo table in SQL.

• Place the OLE DB Source to read the Access table and configure it as an image in screenshots 20 and # 21 . Place a Derived Transformation to convert the string to Unicode, as shown in screenshot 22 . Place the Lookup transformation to retrieve the CompanyId based on the name and address and configure the task as shown in screenshots # 23 and # 24 . The default search task will fail if it cannot find a match. We need to normalize the Widget data. So, put the Unpivot transformation and set it up as shown in screenshot # 25 . Place the OLE DB Destination to insert the data into SQL and configure it as shown in screenshots # 26 and # 27 . The second task of the data flow will be as shown in screenshot # 28

• Screenshots # 29 - # 31 show an example of package execution.

• Screenshot # 32 shows the data in the SQL tables after the package execution.

I hope this should give an idea of ​​exporting data from an Access database to an SQL server. You can group tables in Access and load them as part of a single data flow task. If there are tables depending on other tables, then you can put the data flow in a separate task, as shown in this example.

Hope this helps.

SQL scripts:

 CREATE TABLE [dbo].[CompanyInfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [nvarchar](80) NOT NULL, [CompanyAddress] [nvarchar](255) NOT NULL, CONSTRAINT [PK_CompanyInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO CREATE TABLE [dbo].[WidgetInfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [CompanyId] [int] NOT NULL, [WidgetName] [nvarchar](40) NOT NULL, CONSTRAINT [PK_WidgetInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO 

Screenshot # 1:

Screenshot 2:

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6:

Screenshot No. 7:

Screenshot # 8:

Screenshot No. 9:

Screenshot No. 10:

Screenshot No. 11:

Screenshot No. 12:

<T411>

Screenshot No. 13:

Screenshot No. 14:

Screenshot No. 15:

Screenshot No. 16:

Screenshot No. 17:

Screenshot No. 18:

Screenshot # 19:

Screenshot No. 20:

Screenshot No. 21:

Screenshot No. 22:

Screenshot No. 23:

Screenshot No. 24:

Screenshot No. 25:

Screenshot No. 26:

Screenshot No. 27:

Screenshot No. 28:

Screenshot No. 29:

Screenshot No. 30:

Screenshot No. 31:

Screenshot No. 32:

Answer 2

This can help:

• You will need connection2 for your source and target database. It looks like you already have your source.

• You will need the data flow task in the Flow Control tab. Drag one of them and double-click - you will be taken to the "data stream" tab.

• here add a “data source” (which sounds like you did) and also add “OLE DB Destination”.

• Double-click your source. You can specify a connection (again, it looks like you did it)

• "Data Access Mode" indicates how you want to receive data. That is, directly from the table or write a query that returns data

• if it is a direct one-to-one mapping to a destination, you should be able to connect the two with a green line (restriction of prefixes).

If you want to do any transformations, you can do this in a soruce request or place one of the transformation objects between the source and end points and connect them to yourself.