Post reply

TIME in SSIS loading as an INTEGER. How can I load it as TIME?

  • September 25, 2009 at 11:45 am

    #135941

    Using SSIS I'm extracting data from an Intersystems cache database and having issues with TIME fields. I'm using a DataReader source Dataflow in which the TIME is successfully extracted but it has an INTEGER datatype in the Input/Output properties. My destination table field is defined as BIGINT and the values get successfully loaded. However, the values in the BIGINT field don't appear to be useful data. I'll get results like 2680000000 or 41860000000.

    I changed the DataReader Output External column to database timestamp [DT_DBTIMESTAMP] and the destination table field to TIMESTAMP but get a validation error in my Destination Dataflow stating, "The OLE DB provider used by the OLE DB adapter cannot convert between types for "DT_DBTIMESTAMP" and "DT_BYTES......" for my field.

    Any help in setting up the SSIS package to get the legitimate TIME field would be helpful. Also, any help in converting the BIGINT time to a relevant time would also be helpful.

  • September 30, 2009 at 11:09 am

    #1060093

    bigclick (9/25/2009)

    Using SSIS I'm extracting data from an Intersystems cache database and having issues with TIME fields. I'm using a DataReader source Dataflow in which the TIME is successfully extracted but it has an INTEGER datatype in the Input/Output properties. My destination table field is defined as BIGINT and the values get successfully loaded. However, the values in the BIGINT field don't appear to be useful data. I'll get results like 2680000000 or 41860000000.

    I changed the DataReader Output External column to database timestamp [DT_DBTIMESTAMP] and the destination table field to TIMESTAMP but get a validation error in my Destination Dataflow stating, "The OLE DB provider used by the OLE DB adapter cannot convert between types for "DT_DBTIMESTAMP" and "DT_BYTES......" for my field.

    Any help in setting up the SSIS package to get the legitimate TIME field would be helpful. Also, any help in converting the BIGINT time to a relevant time would also be helpful.

    I suspect the number you receive is ticks from a fixed starting time. The .NET DateTime type has a constructor with Int64 parameter. Implement a transformation script component , which takes your BIGINT number, constructs a new DateTime object with it and then pushes the DateTime object to your output as a new column with type DT_DBTIMESTAMP.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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