Does SSIS not handle the SQL variant type on transfer?

Question

Post reply

Does SSIS not handle the SQL variant type on transfer?

md.noorullah

Say Hey Kid

Points: 691
More actions
January 9, 2009 at 5:42 am

#125426

Hi,
I have to get records of one table which is in different server to another table of another server..
I am using Data flow task to pull records..but the problem is one of the column is of type "SQL_Variant".
I am getting this following Warning:
Validation warning. DFT-TABLEA: {8872BE20-F843-4D47-A2A1-D6364E528FA8}: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.
Can any one enlighten me... 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

md.noorullah Say Hey Kid Points: 691 More actions · Answer 1

md.noorullah

Say Hey Kid

Points: 691

January 11, 2009 at 11:51 pm

#925119

Please any one help me out!!!!:crazy:

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

Based on the error I would say that SSIS does handle SQL Variant, but gives a warning to let you know it is handling it using a unicode string data type. Is this keeping your package from working? Why are you using SQL Variant in the database?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

md.noorullah Say Hey Kid Points: 691 More actions · Answer 3

Thanks for the reply..

That is the requirement..we have to pull the records of sql_variant column also.

Package is running fine. But I want to avoid the warning also.

Is there any other way in the implementation..

Thanks,

Noor........

shuzi Say Hey Kid Points: 684 More actions · Answer 4

Hi,

I have the same warning. The package i try to create is export data to excel sheet. one of the column is sql_variant. Failed.

Can someone help?

thanks

nara20 Valued Member Points: 69 More actions · Answer 5

Hi,

Same problem : I have an sqlvariant in my source table, and so how to avoid the warning "...an external data type that cannot be mapped to a Data Flow task data type..."

Thanks for advance for yours answers

mike 38908 SSC Journeyman Points: 89 More actions · Answer 6

I know this post was written almost 3 years ago. . . .but I just found a solution to this that I haven't seen anywhere on the internet.

What I discovered is that your package will error out with an MDX query in the SQL Text box if you are connecting to your SSAS datasource through a Shared Data Connection, but if you just add the data connection directly in the connection manager, and connect your data source component to that, then you will still get the warnings about Data Type conversion, but the task will execute perfectly.

I felt obligated to post this, just in case anyone is still having the problem. Have a nice day!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

mike 38908 (2/3/2012)
I know this post was written almost 3 years ago. . . .but I just found a solution to this that I haven't seen anywhere on the internet.
What I discovered is that your package will error out with an MDX query in the SQL Text box if you are connecting to your SSAS datasource through a Shared Data Connection, but if you just add the data connection directly in the connection manager, and connect your data source component to that, then you will still get the warnings about Data Type conversion, but the task will execute perfectly.
I felt obligated to post this, just in case anyone is still having the problem. Have a nice day!

This feels like a solution for SSAS, while this is an SSIS thread.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 8

It seems logical to use a sqlvariant given the horrors of importing a field where the data looks like this:

Column

1:1

1:1 Considered

1:2

2:1

Undetermined

What shows up in the download are blanks where the value cannot be converted to time regardless of whether the use of a derived column or a straight shot in. Thought I would try a sqlvariant but the type doesn't appear to be recognized. At least it is not recognized as a TYPE CAST in the derived column transformation editor.

Jamie

Phil Parkin SSC Guru Points: 246731 More actions · Answer 9

Jamie-2229 wrote:

It seems logical to use a sqlvariant given the horrors of importing a field where the data looks like this: Column 1:1 1:1 Considered 1:2 2:1 Undetermined What shows up in the download are blanks where the value cannot be converted to time regardless of whether the use of a derived column or a straight shot in. Thought I would try a sqlvariant but the type doesn't appear to be recognized. At least it is not recognized as a TYPE CAST in the derived column transformation editor.

Why not use a VARCHAR() for the data you mention?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.