Post reply

Does SSIS not handle the SQL variant type on transfer?

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

  • January 11, 2009 at 11:51 pm

    #925119

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

  • January 12, 2009 at 7:22 am

    #925251

    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

  • January 12, 2009 at 9:40 pm

    #925651

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

  • August 5, 2009 at 11:46 am

    #1034992

    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

  • August 7, 2009 at 8:26 am

    #1036031

    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

  • February 3, 2012 at 3:32 pm

    #1442678

    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!

  • February 6, 2012 at 12:40 am

    #1442915

    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

  • May 13, 2019 at 6:32 pm

    #3641637

    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

  • May 13, 2019 at 9:51 pm

    #3641688

    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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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