Does SSIS not handle the SQL variant type on transfer?

  • md.noorullah

    Say Hey Kid

    Points: 691

    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... 🙂

  • md.noorullah

    Say Hey Kid

    Points: 691

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

  • Jack Corbett

    SSC Guru

    Points: 184377

    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

    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

    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

    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

    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: 258955

    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: 8151

    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: 243791

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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