1

I'm trying and playing around a CSV file to import data into the SQL Server table using SSIS. The package is simple with File Source Task and SQL Server Destination.

The CSV file has 2 fields Transaction_Date and Account_Created. The dates in these fields are the format of 1/2/2009 6:00:00 AM. I am seeing the below error message: "Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type datetime of the specified target column."

Steps I tried below: I tried using various other destination transformations. I tried playing around the Data Types inside the Flatfile Connection Managers. I tried using the Data Conversion Transformations between the Source Task and SQL Server Destination.

When I tried to load the data by providing connection only to Transaction_Date it works. However, when I tried to load by providing connection only to Account_Created it fails.

I am sure I'm missing something silly. Please help.

Regards, KK

I tired a different method to build the package from start using the Wizard. I used the actual CSV file which had many other columns like Price, Product_name and so on. When I tried to execute the package I see a different error message as below: "[Destination for AdventureWorks [58]] Error: There was an error with input column "Price" (91) on input "OLE DB Destination Input" (71). The column status returned was: "The value could not be converted because of a potential loss of data.". " When I tried a CSV file with only 2 date fields it worked excellent.

I am really puzzled now and thinking this is some kind of data type issues which I am not getting it correct. Can some one pls shred some light into this problem?

Regards, KK

Improve this question
1
  • 1
    You definitely need to use a data conversion transformation. Does 1/2/2009 mean 1st Feb or 2nd Jan? As an example, the SSIS expression (DT_DBDATE) "2009-01-02" results in a date. You need to perform some string manipulation on your data file to get your date into that format and then use (DT_DBDATE) to convert it to a date data type. Commented Feb 11, 2013 at 6:29

1 Answer 1

Reset to default
2

To load the first two fields (Transaction_Date, Account_Created), you need a DataFlow Task that contains:

  • Flatfile Source
  • Derived Column (create two columns to Replace 'Transaction_Date' and 'Account_Created' with formula below)
  • SQL Server Destination

Notes:

  • Date format like '1/2/2009 6:00:00 AM' is not parsed by SSIS, make sure the Flatfile Connection Manager treats the fields as Strings (length > 22)

  • In Derived Column, you can parse '01/02/2009' with this formula:

    (DT_DBDATE)(SUBSTRING([Column 2],7,4) + "-" + SUBSTRING([Column 2],4,2) + "-" + SUBSTRING([Column 2],1,2))

  • The current date format that you have in the file '1/2/2009' makes the conversion very tricky due to the lack of advanced datetime parsing functions of SSIS. Depending on the day and month you will have to subtract from a variable length string, therefore you will have to combine SUBSTRING with FINDSTRING to determine the position of the separator '/'

Good luck

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.