SSIS data flows and tinyint

If you have designed your db well and are using appropriate datatypes, rather than int every where it is likely you will have used the tinyint datatype for your small sets of data. If you have subsequently tried to use these in SSIS its not happy.

I've come across this before and not found the answer so when I can up with the same issue again recently I thought I would dig a bit more.

Douglas posted a while back in response to a forum post with details about an update to BOL. This update is now in BOL (http://msdn2.microsoft.com/en-us/library/ms345165.aspx) and mentions the mapping between SSIS types and .Net manager types. It doesn't discuss mapping to SQL types. It does mention mapping files used for the import export wizard which are located at file://C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles, but it does state that these are only used by the import export wizard. However I thought I would look at them any way.

I cam across one file SqlClientToSSIS.xml which has the mappings it looks like I want.

<dtm:DataTypeMapping>
  <dtm:SourceDataType>
    <dtm:DataTypeName>tinyint</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
    <dtm:CharacterStringType>
      <dtm:DataTypeName>DT_UI1</dtm:DataTypeName>
      <dtm:Length>3</dtm:Length>
    </dtm:CharacterStringType>
  </dtm:DestinationDataType>
</dtm:DataTypeMapping>

So unlike the other sql integer types the tinyint gets converted to an un-signed single byte integer. 

So there you have it, if using tinyints you need to use the SSIS type DT_UI1 (single byte un-signed integer)

-
Published Friday, February 24, 2006 5:46 AM by simonsabin
Filed under:

Comments

Tuesday, September 22, 2015 9:21 AM by TrackBack

#

Pingback from