Post reply

SSIS Data Type

  • November 20, 2008 at 3:40 am

    #128830

    Hello All,

    I am importing a flat file (CSV file) into SQL2005 DB.

    The row looks like:

    "MANAGERS LTD","B","19 NOV 2008"

    The SSIS Error:

    Cannot create an OLE DB accessor. Verify that the column metadata is valid.

    Now im importing a similar row in a differant file which looks like:

    "MANAGERS LTD","B","19-NOV-2008" <------- Noitce the - between date-month-year

    this seems to work fine!

    Any ideas...going made here!!! :crazy:

  • November 20, 2008 at 4:01 am

    #900959

    what i would do is;

    Import the csv file as set all the fields to import a string,

    do some transformation on the suspect dates to set them to a similar format

    Convert these strings to a datetime format.

  • November 20, 2008 at 4:27 am

    #900967

    Thanks,

    easier said than done... im a newbi to SSIS...

    Any tips?

  • November 21, 2008 at 2:31 am

    #901499

    Ok to out line the issue:

    Importing a flat file (.CSV) via SSIS into SQL2005 DB.

    The Flat file row looks like:

    "MANAGERS LTD","B","19 NOV 2008","1.249547"

    The error is with the date column

    Within the SQL DB the column is set to 'SmallDatetime'

    I think the column is exspecting the date to be 19-Nov-2008

    - How can I do a data conversion/transformation?

    - Or is there another way around this?

    p.s. I cant change the flat file content!

    Thanks

  • November 21, 2008 at 4:51 am

    #901530

    Add a script component to your code to do the convertion.

    In my script component I've added a reference to a dot net program that i wrote which basically checks for a valid date and converts it into the correct format again.

    In the script input column i have date_in (char8) and in the output column have date_out as (dt_dbdate)

    in the script compont i have

    Imports Utils.ASD

    .....

    Row.DATEout = Util.ToSmallDateTime(Row.DATEIn)

    ....

    in my dot net program

    have a function

    Public Function ToSmallDateTime(ByVal sPassedDate As String) As DateTime

    which basically does the check on the date if it can be converted , if not return a default etc.

    You can add this function to your code and check for a space and replace with "-", which is what the ssis package expects.

    Good reason to have an external function is that all your packages can access the same function and dont have to recode everytime

    hope this helps

  • November 21, 2008 at 12:26 pm

    #901838

    The vast majority of my data sources are CSV files, and I always bring them into the data flow as strings, then validate them and convert them to their appropriate destination data type as needed through scripts if a lot of logic is required or through derived column transformations if not.

    For example, when reading in a 8-character string date without separators and converting it to a dt_date, I might use the following expression in a derived column:

    (DT_DATE)(SUBSTRING(AdmitDate,1,2) + "/" + SUBSTRING(AdmitDate,4,2) + "/" + SUBSTRING(AdmitDate,6,4))

  • November 24, 2008 at 12:53 am

    #902303

    THANKS ALL for the info!

    Is there a way of creating a SQL SP to do the data conversion?

    (Please see the attached pic).

    As the import is occuring in the DataFlow do I have to use a OLE DB Command or Script Component?

    Thanks

  • November 24, 2008 at 3:27 am

    #902346

    Since you are using SSIS to do the import you are better off using SSIS to do the conversion.

    You need to add Data Conversion Transformation between your data source and desination to convert from a string into a datetime. Add the transformation and connect the data flow from the souce to conversion transfomration, then select the coumn to convert and output this column to the destination.

    Or you could use BCP or Bullk-Import to do this data load , then use a SQL procedure with a CAST to do the data conversion.

  • November 24, 2008 at 3:51 am

    #902352

    SteveB - Thanks for the clear reply!!

    Looks like I have fixed the date issue by using DataType 'Unicode string [DT_WSTR]'

    :crazy: ...Now I have an issue with a column which holds the values 3.144591

    Im getting the error:

    Error: 0xC02020A1 at ImportFiles, Profit [3864]: Data conversion failed. The data conversion for column "Column 4" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    Error: 0xC020902A at ImportFiles, Bargains [3864]: The "output column "Column 4" (3881)" failed because truncation occurred, and the truncation row disposition on "output column "Column 4" (3881)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    Error: 0xC0202092 at ImportFiles, Profit [3864]: An error occurred while processing file "E:\Files\profit.CSV" on data row 440.

    Error: 0xC0047038 at ImportFiles, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Bargains" (3864) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    BUT... this seems to work for another SSIS package I have importing the same value using the same data type: string [DT_STR].

    Any ideas, agian, thanks for your time!

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

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