Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

MS Access Table for SSIS

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.

New SSIS connection

Select ODBC connection type from the list

New ODBC Connection SSIS

Click New to create a New ODBC Connection

New ODBC Connection Setup

Click Build to create a new DSN to the Microsoft Access database.

Build DSN to Connect to MS Access

Click on New to create the New DSN

Create 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.

MS Access ODBC Setup MS Access DB Select

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.

Set up Data Flow Task

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.

ODBC Source to Data Flow

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.

Set Mappings from ODBC to OLE SQL

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 IssuePackage Error Code

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.

Add Data Conversion Task to Data Flow in SSIS

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).

Converting data in Data Conversion Step

Execute the Task. The Green checks indicate that the data was converted and transfered into the SQL Server 2012 database table.

Test SSIS Package

A quick query of the SQL table shows the data made it into the table as expected.

Data Verification post SSIS Package Execution

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.

Configure Error Output