Post reply

SSIS question

  • April 21, 2010 at 3:05 pm

    #139886

    I have OrderSent column(timestamp datatype) in AS400 file with the below values:

    2009-03-11-13.07.24.922000

    2009-02-18-13.07.24.924000

    When I am trying to load this AS400 column using SSIS into a SQL column with datetime datatype, I am getting the below error:

    Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005

    Description: "Invalid character value for cast specification".

    Any ideas??

    Thanks.

  • April 21, 2010 at 3:08 pm

    #1155700

    Remove the hyphen between the date and time.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 21, 2010 at 7:25 pm

    #1155804

    How I can remove the hyphen? Thanks.

  • April 22, 2010 at 2:30 am

    #1155915

    Read the column in as text.

    Use a derived column component to chop up the string into 2 substrings, one before and one after the hyphen that you want to remove (use findstring combined with substring). Then paste those two substrings together with a space in between. Then convert it to datetime.

    I think you just can't use the replace function, because it will replace all hyphens.

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

  • April 22, 2010 at 8:08 am

    #1156129

    I am new to SSIS, can you send me the expression which I can use in the derived column component or refer me to some article. Thanks.

  • April 22, 2010 at 9:21 am

    #1156204

    I wrote this expression for the AS400 date in the derived column component to show in the ISO format standard "yyyy-MM-ddThh:nn:ss.fffff"

    SUBSTRING(DerivedCol,1,4) + "-" + SUBSTRING(DerivedCol,6,2) + "-" + SUBSTRING(DerivedCol,9,2) + "T" + SUBSTRING(DerivedCol,12,2) + ":" + SUBSTRING(DerivedCol,15,2) + ":" + SUBSTRING(DerivedCol,18,9)

    but even then I am getting this error:

    Source: "Microsoft SQL Server Native Client 10.0"

    Hresult: 0x80004005 Description: "Invalid character value for cast specification".

    any ideas?

  • April 22, 2010 at 1:02 pm

    #1156383

    Any help is appreciated!!

  • April 22, 2010 at 2:03 pm

    #1156420

    You need to type cast your expression to a date datatype:

    (DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + (SUBSTRING(DateValue,6,2) + "-" + SUBSTRING(DateValue,9,2) + " " + SUBSTRING(DateValue,12,2) + ":" + SUBSTRING(DateValue,15,2) + ":" + SUBSTRING(DateValue,18,9)))

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • April 22, 2010 at 3:11 pm

    #1156462

    Thanks! it worked.

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

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