CODEWITHSUNDEEP
sql-servercsvssis
xyzcode
xyzcode

Reputation: 219

SSIS date, comma, and derived column issues

I’m brand new to SSIS and I'm building a package where I have a Flat File source coming from a CSV and importing it into our database. In the CSV the column I'm having problems with is called Date (dates are formatted as dd-mm-yy, ex: 8-feb-16), I've had SSIS suggest types and it gives that column a type of string. In the database the corresponding column for Date has a datatype datetime which equates to it having a type of database timestamp in SSIS. I’ve tried giving them the same datatype, tried running it through Data Conversion, I’ve messed with them quite a bit but nothing seems to work. Maybe I’m missing something? The errors I'm getting are:

The data conversion for column ""Date"" returned status value 2 and status text 
The value could not be converted because of a potential loss of data.

The other issue is that there are some commas in the table data but they appear throughout random columns. Is there a succinct way to search all rows to remove commas? Using a Derived Column for every column in my CSV is really inefficient but I can't seem to find a better answer on this.

Upvotes: 1

Views: 741

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

SSIS is very picky about the formats it allows for strings to convert to other data formats. Take a look at the doc on Data Types, and you'll see under the heading Converting Between Strings and Date/Time Data Types that converting a string to a date/time type supports these formats:

+----------------------+---------------------------------------------+
|      Data type       |                String format                |
+----------------------+---------------------------------------------+
| DT_DBDATE            | yyyy-mm-dd                                  |
| DT_FILETIME          | yyyy-mm-dd hh:mm:ss:fff                     |
| DT_DBTIME            | hh:mm:ss                                    |
| DT_DBTIME2           | hh:mm:ss[.fffffff]                          |
| DT_DBTIMESTAMP       | yyyy-mm-dd hh:mm:ss[.fff]                   |
| DT_DBTIMESTAMP2      | yyyy-mm-dd hh:mm:ss[.fffffff]               |
| DT_DBTIMESTAMPOFFSET | yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] |
+----------------------+---------------------------------------------+

That's your only option for formats. If your values don't match that format, you've got to make them match that format.

Your best bet is to import the field as a DT_WSTR, and use a Derived Column with an expression to do your translations.

The other option would be to import them into a staging table as a string, and then convert them from there.

Upvotes: 1

Related Questions