none
Is It Possible to Test for Data Type in an Expression? RRS feed

  • Question

  • Hello, all -

    I'm quite sure everyone is well aware that, when inputting from an Excel source, SSIS frustratingly tests the first dozen or so rows of data and decides the data type on its own, ignoring any settings one may have set in the Advanced Editor.

    This is not a big problem, if the data is consistent. One can slap in a Derived Column task to convert data as needed.

    But, what if the data changes? What if the presumed data type in that collection of rows that SSIS tests changes? SSIS will - I'm guessing - alter the data type of the changed column and that could screw up all sorts of things down stream.

    So, my question is, is it possible to test for a column's data type? That way, if something happens and SSIS decides that the column is, say, a date field, when you've already set the thing up to massage string data to pull out a date, I'll be able to process it as a date data type, rather than text.

    Or, am I full of beans and making a presumption that will never happen?

    Thanx in advance for any assistance!

    Tuesday, December 10, 2019 5:51 PM

Answers

  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    No, nothing to update. However, it looks like it is not possible to check the data type programmatically. Checking it with the advanced editor is not a solution, since that is at design time and I need to be able to test the type at run time.


    Tuesday, December 17, 2019 1:02 PM

All replies

  • Hi Adam,

    SSIS is metadata driven.

    It means whatever MS Excel emitted during development time in Visual Studio, SSIS package will store these data types metadata statically inside the package. And it will expect the same data types during run-time. And it will fail if there is a change on the Excel file level.

    On the other hand, MS Excel doesn't have any notion of data types. It is like a virtual electronic paper. It tolerates any scribble on it.

    That's why it is better not to use MS Excel files for data feeds.

    It is much better to use XML files enforced by an XML Schema for that.


    Tuesday, December 10, 2019 8:15 PM
  • Hi Adam,

    We could check the data type of the column in Advanced Editor for source.

    We can use Data Conversion Transformation to change the data type as we need.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 11, 2019 2:01 AM
  • Hi Adam,

    We could check the data type of the column in Advanced Editor for source.

    We can use Data Conversion Transformation to change the data type as we need.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Yes, but that is all manually done. I'm looking for something to test for in an expression for use in a Derived Column task, for example, that can test the data type with an IF statement and then process it accordingly by type.
    Wednesday, December 11, 2019 8:32 PM
  • Hi Adam,

    It seems that we cannot test the data types of columns in Derived Column Transformation.

    It's better for you to check the data types of columns in Advanced Editor.

    We can use Data Conversion and Derived Column to change the data types.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 9:45 AM
  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 8:28 AM
  • Hi,

    May I know if you have anything to update?

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    No, nothing to update. However, it looks like it is not possible to check the data type programmatically. Checking it with the advanced editor is not a solution, since that is at design time and I need to be able to test the type at run time.


    Tuesday, December 17, 2019 1:02 PM