SSIS Error to SQL Azure with varbinary(max)

  • Article

[This article was contributed by the SQL Azure team.]

In using the SQL Server Import and Export Wizard found in SQL Server 2008 R2 to export data to SQL Azure, I noticed that it was having trouble with a column that was the data type of varbinary(max) . This article will discuss how to the fix the issue I encountered in hopes that it will help you.

The Problem

In using the SQL Server Import and Export Wizard, I was using a .NET Framework Data Provider for SqlServer data source to connect to SQL Azure, and a SQL Server Native Client 10.0 data source to connect to my local server. I was exporting from my local server into SQL Azure. I described the process of using the SQL Server Import and Export Wizard in this blog post.

When exporting one particular table that had a varbinary(max) column, the SQL Server Import and Export Wizard (using SSIS) was throwing a red icon (critical error unable to export), because the .NET Framework Data Provider for SqlServer didn’t know how to translate the varbinary(max) column. The error I was getting was this:

SSIS Type: (Type unknown ...)

clip_image002

Background

When SSIS goes between two dissimilar data sources it must convert the data types of one data source to the other. To do this, it uses a set of mapping files found in: C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles. The two files involved in my case are: SqlClientToSSIS.xml and MSSQLToSSIS10.xml. The SqlClientToSSIS.xml was the one being used by the .NET Framework Data Provider for SqlServer data source to translate the datatype, and the SQL Server Native Client 10.0 data source was using MSSQLToSSIS10.xml.

Solution

With the SQL Server 2008 R2 release the MSSQLToSSIS10.xml file has a definition for varbinarymax, the dtm:DataTypeName element. However, the SqlClientToSSIS.xml file doesn’t contain a definition for varbinarymax. This leaves SSIS unable to map this data type when moving data between these two providers. The solution was to add this definition to the SqlClientToSSIS.xml file:

   <!-- varbinary (max) -->
  <dtm:DataTypeMapping >
    <dtm:SourceDataType>
      <dtm:DataTypeName>varbinarymax</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
      <dtm:SimpleType>
        <dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
      </dtm:SimpleType>
    </dtm:DestinationDataType>
  </dtm:DataTypeMapping>

Note: I had to grant myself write permissions to SqlClientToSSIS.xml in Windows Explorer so that I could modify it.

Do you have questions, concerns, comments? Post them below and we will try to address them.