November 19, 2008 at 5:34 am
Hello,
We are using a CSV to import data into a database, and the CSV contains a date in the format of "YYYYMMDD" as a length of 8 (without seperators). When we insert this straight into the database as a string, it fails, even though manually inserting through a SQL query works fine.
So, after a bit of research, it seemed that we had to use a Derived Column expression to add the seperators in - this worked. However, sometimes the CSV contains "00000000" or nothing at all and this is where the insert fails.
I figure I have to check if the value is "00000000" and set it to NULL, if not then format it with the seperators. But how can I check if it is NULL, and how do I not get the package to fail if it is?
Many thanks,
Ashley S.
November 19, 2008 at 6:38 am
You can check for NULL in your expression for your derived column, but it may be better to check for an appropriate length.
The expression will be something like:
LEN(ColName)!=8 ? "" : ColName
What you should do is use a data conversion component to convert the column to a date (or use a type cast in your derived column). You will then be able to use the error configuration to ignore errors - this will leave the column NULL if it cannot convert it to a date. I am not sure if the data conversion component will convert directly from YYYYMMDD to a date, but even if you have to use a derived column to add slashes or hyphens, doing the conversion in SSIS before it goes to the output will be better.
November 19, 2008 at 6:56 am
Hello, thank you for your reply.
I have tried doing this, but it still fails when the CSV doesn't contain a value or if it is set to "00000000". I have set the datatype to timestamp before and it fails before it even gets to the Derive Column.
November 19, 2008 at 7:59 am
Sorry to hijack this thread, but I'm having a somewhat similar problem. I'm importing from a csv file into a Sql Server 2005 database, and the program that creates the csv puts "01/01/1800" for any date that is really NULL. I'm trying to write a formula to convert that date to NULL, but have been unsuccessful (I'm an SSIS newbie).
I defined the column in my flat file connection as DT_DATE. I'm importing it to a Datetime column in Sql Server. I'm trying to do the derived column so that it replaces the existing column prior to moving it to Sql Server (not creating an additional column). The Derived Column Transformation Editor looks like this:
Derived Col Name Date Booked
Derived Col Replace "Date Booked"
Expression [Date Booked] == "01/01/1800" ? NULL : [Date Booked]
The expression turns red when I move off the field. The error text looks like this:
TITLE: Microsoft Visual Studio
------------------------------
Error at extract acContractInfoDwn [Derived Column [2559]]: Parsing the expression "[Date Booked] = "01/01/1800" ? NULL : [Date Booked]" failed. The equal sign (=) at line number "1", character number "15", was not expected. A double equals sign (==) may be required at the location specified.
Error at extract acContractInfoDwn [Derived Column [2559]]: Cannot parse the expression "[Date Booked] = "01/01/1800" ? NULL : [Date Booked]". The expression was not valid, or there is an out-of-memory error.
Error at extract acContractInfoDwn [Derived Column [2559]]: The expression "[Date Booked] = "01/01/1800" ? NULL : [Date Booked]" on "input column "Date Booked" (2813)" is not valid.
Error at extract acContractInfoDwn [Derived Column [2559]]: Failed to set property "Expression" on "input column "Date Booked" (2813)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
November 19, 2008 at 9:01 am
got it solved elsewhere, expression should be:
[Date Booked] == (DT_DATE)"01/01/1800" ? NULL(DT_DATE) : [Date Booked]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply