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.

i'm seeing the wrong datatypes in both...
0 Likes 0 ·
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 ·
I'm using union all so in the tables where the column doesn't exist i'm passing NULL...
0 Likes 0 ·
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 ·
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 ·
Show more comments
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.