I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:
"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"
The value of 00-XXX-00 means that there is no purchase date.
I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.
The table Data Type is datetime.
If I use the import wizard using the example above I get this error message:
- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)
If I remove the 00-XXX-00 values and import something like this:
"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"
The import is successfull and the dates look correct in a querry.
SELECT *
FROM date
date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000
(3 row(s) affected)
Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?
Thank you,
Ryan
Ryan,
The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.
In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.
You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:
SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>
Hope that helps!
-Jamie
|||Could you please break down what that expression does or point me to a reference for the expression language?|||
Don't mean to butt in here, but what Jamie has listed here:
Jamie Thomson wrote:
SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>
-Jamie
uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.
So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.
The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.
Hope this helps.
Mark
http://spaces.msn.com/mgarnerbi
|||There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.
-Jamie
