question

jhowe avatar image
jhowe asked

SSIS ole db source assigning wrong datatype

Hello I have 8 data sources executing stored procs (all similar) feeding into a union all component. For some stupid reason SSIS keeps assigning DT_14 datatype to nvarchar columns (i'm passing as NULL) and no matter what I do it keeps resetting back to DT_14 even if I change it to DT_WSTR! It's extremely annoying! please help... I have tried going into advanced edit on sources and manually changing datatype etc... how do i force SSIS to set a different datatype in the ole db source?
sql-server-2008ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the_SQL avatar image
the_SQL answered
Where are you seeing the inccorect data types? In the External or Output columns of the OLE DB source?
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
i'm seeing the wrong datatypes in both...
0 Likes 0 ·
the_SQL avatar image the_SQL commented ·
I can only speak on SQL Server 2005, as I do not have a 2008 instance at my disposal at the moment. Typically, the External Column datatype is derived from the data source column. In fact, if you try to change the data type to a type that is incompatible with the source column, BIDS will throw an error.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
I'm using union all so in the tables where the column doesn't exist i'm passing NULL...
0 Likes 0 ·
jhowe avatar image jhowe commented ·
If i try changing both the external and output column to the correct datatype i get another stupid error saying the error output has properties that do not match bla bla, which i don't even care about but it won't let me change it!
0 Likes 0 ·
jhowe avatar image jhowe commented ·
na we don't want blank space filling up the rows/columns etc... what I might have to do as I can't find a solution is using declare @variable nvarchar set = NULL at the start of my stored procedures... seems really hacky and stupid to me...
0 Likes 0 ·
the_SQL avatar image the_SQL commented ·
The reason that I say that, is because the external columns will sample the return from the stored procs. It will assign a data type based on the data in the return. NULL is not a charatcer data type. It is assigning a 4 byte int, because that is the data type SSIS associates with a NULL value.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
yea I was getting to that conclusion... my column 'event' which is a dt_wstr is being passed as nulls in my other data sources, so when I try to join it all together in union all it's blowing up because it's trying to union a dt_14 with dt_wstr...
0 Likes 0 ·
the_SQL avatar image the_SQL commented ·
You could allow the type inbound, if you want to keep the null values, and add a data conversion transorm in the data flow that will update the type in-line, before it reaches the destination.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
there's only outputs on ole db source, I may have to manually add null as a derived column or something, not sure how else to do it...
0 Likes 0 ·
the_SQL avatar image the_SQL commented ·
What I meant was that you could allow the data type coming from the source. After your union all transform, you could add the data conversion transform to convert the DT_I4 to DT_WSTR before it reaches the destination. You do not have to change your SP that way.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Oh i see... (i'm still fairly new to SSIS) i'll try that!
0 Likes 0 ·
the_SQL avatar image
the_SQL answered
@jhowe, try the following (If you have the ability to still make changes) in your stored proc. I found some updated information that may make your life (and SSIS package) a little easier: For tables, SSIS takes the underlying data type. For stored procs, SSIS must read the first SELECT statement to retrieve metadata for what it expects the stored proc to return. In your stored proc, use a no-op select statement to declare the meta-data for the given columns, i.e. ---------- SELECT CAST(NULL AS ) AS ColumnName, CAST(NULL AS ) AS ColumnName, CAST(NULL AS ) AS ColumnName ...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.