May 10, 2011 at 3:53 pm
I have a excel source which has dates like
5/7/2009
11/24/2008
8/25/2009
The additional line spacing implies no values in those columns in teh excel file.
I have a derived column for this one, which maps it as date[DT_DATE]
The destination column in the SS DB is of type datetime and allows NULLS
When i run the DFT the package stops with the following error
An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
Error: 0xC0209029 at Data Flow Task, Derived Column [11908]: The "component "Derived Column" (11908)" failed because error code 0xC0049062 occurred, and the error row disposition on "output column "LastDate" (12009)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Derived Column" (11908) 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.
Any ideas, FYI when i dragged the column to create a derived column the SSIS itself picked up the DT_DATE formate
🙂
Thanks in advance
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 11, 2011 at 2:11 am
You need to get rid of those empty lines first, and then place your derived column.
You can use a conditional split for that, or use a SQL query in the Excel Source. In the SQL query, you can add the following where clause:
WHERE LastDate IS NOT NULL
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 11, 2011 at 2:17 am
Presumably, there is other data on the lines with no dates which you want to keep? If so, you can't use Koen's solutions, as they'll discard the entire row.
From the error message, I cannot tell whether the error is due to the blanks or due to the dates - can you verify which is causing the problem?
Either way, you may find that you can import the column as text and then base a derived column on it to get the required date.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 11, 2011 at 2:29 am
Phil Parkin (5/11/2011)
Presumably, there is other data on the lines with no dates which you want to keep? If so, you can't use Koen's solutions, as they'll discard the entire row.From the error message, I cannot tell whether the error is due to the blanks or due to the dates - can you verify which is causing the problem?
Either way, you may find that you can import the column as text and then base a derived column on it to get the required date.
Phil is correct. If you have other columns, you will be throwing data away with my solution (that will teach you to not specify things better in your question! *demonic laughter*)
You can use the following expression in the derived column:
ISNULL(LastDate) ? NULL(DT_DATE) : (DT_DATE) LastDate
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 11, 2011 at 3:06 pm
None of the above solutions worked , after a coupel of hours of head bangigng...
Firgured out that I had invalid date formats which appeared valid .. that is ..
Few thousand cells looked empty but actually have had a single space ..
Once i removed the single space was able to map directly even with out a derived column.
Thank you so much for your insight and comments they helped me in different ways though.
and finally.. YES i shd have posted it more meaningfully , I DO HAVE DATA THAT I CARE FOR IN OTHER COLUMNS WHICH HAVE THE DATE COLUMN EMPTY.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply