SSIS Data Conversion Task - MS Access to SQL Server
SSIS Data Conversion Task Package
Using SSIS, this tutorial will show how to create a Package that will push data from an MS Access table through a Data Conversion Task to table in a SQL Server. A Data Transform Task works great if the data types are similar. This article uses an MS Access table memo field which resolves as DT_NTEXT in SSIS to a field in a SQL Server table NVARCHAR field. Data Conversion is necessary when data types conflict with one another and do not transform without errors. This task will show how to convert the data types to get the transfer to work correctly.
SSIS Project Data Conversion Task
To begin, opening Visual Studio 2010, select a new BIDS project. Add Data Flow Task to the Control tab.
SSIS Connection Setup
The connections will be an ODBC Connection (MS Access) and an OLE Connection (SQL Server 2012). In MS Access 2007, a table named Table1 has 2 fields: Field1 Text 255 char maximum, Field2 Memo - equivalent of NText in SQL Server.
The SQL Server 2012 table is named tbl and has 2 fields: Field1 of type Varchar and Field2 of type nvarchar.
CREATE TABLE tbl
(Field1 varchar (255) NULL,
Field2 nvarchar(4000) NULL
)
Add ODBC connection for MS Access: Right click in the Connection Manager section and select New Connection.
Select ODBC connection type from the list
Click New to create a New ODBC Connection
Click Build to create a new DSN to the Microsoft Access database.
Click on New to create the New DSN
Walk through the steps to create the connection to the MS Access database and Name the DSN. At the end of the process, you will Select the database one more time to set it up.
Add an OLE Connection to SQL Server 2012 in a similar fashion as the ODBC database connection by right clicking the Connection Manager and selecting OLE.
Data Transform Task that will Fail
Next, add a Data Flow item to the Control Flow. This will be a test to prove that these different data types will not transform from the source to the destination without a conversion.
Go to the Data Flow tab and add an ODBC Source and Right Click on it. Select the data source & Table from the Connection Manager and select the Columns.
Add a SQL Destination to the Data Flow tab. Select the SQL Destination Database and tbl. Hover over the ODBC Source and pull the green arrow to connect it to the OLE SQL Destination. Set the Mappings between the fields.
Immediately, you can see the red X in the destination showing that it cannot convert between the Unicode type in MS Access and the Non Unicode type in SQL Server. When you Execute the Task, it fails.
SSIS - Data Conversion Task
Add a Data Conversion task to this Data Flow. Remove the connector from the Source to the Destination and reattach them as shown - Source to Data Conversion, Data Conversion to Destination.
Right click on the Data Conversion Task to set up new data types for the input source fields. Click on each field name and notice that an Output Alias field is now available. Change the Data Type from MS Access Field1 from DT_WSTR to DT_STR (a unicode string to a text string for inserting into the VARCHAR field on SQL Server). Change Field2 from DT_NTEXT to DT_WSTR with a Length of 4000 (a SQL NTEXT field to an NVARCHAR field conversion).
Execute the Task. The Green checks indicate that the data was converted and transfered into the SQL Server 2012 database table.
A quick query of the SQL table shows the data made it into the table as expected.
NOTES: There was a truncation issue when transferring the data out of MS Access. I simply went to the Data Conversion task, right clicked Edit, and clicked on Configure Error Output. From here, you can elect to Fail a Component for an Error or Truncation or elect to Ignore the Failure. There is also an option to Redirect the Row using the Red Arrow output - possibly to Excel or another destination.
