May 14, 2017 at 9:21 pm
Hello,
First of all, I'm an SSIS Noob, and not great with Excel either, someone else created and imported the data into the spreadsheet, and they have left the company. I hope someone can help, I'm at my wits end with this. I have an Excel 2010 file with many columns, one of which is a date column in the following format...
OnTime
| 25/10/2015 10:01:08 PM |
I have tried using a derived column using the following expression to get this in to the datetime column in my empty SQL 2012 database table.
LEN(TRIM(OnTime)) == 1 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(OnTime,1,4) + "-" + SUBSTRING(OnTime,5,2) + "-" + SUBSTRING(OnTime,7,2) + " " + SUBSTRING(OnTime,10,2) + ":" + SUBSTRING(OnTime,12,2))
Now, this may work, but I have empty cells in the column, which MIGHT be the issue, I am not sure. The errors I get from SSIS are...
[Derived Column [2]] Error: The conditional operation failed.
[Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Inputs[Derived Column Input].Columns[TimeOn]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). 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. There may be error messages posted before this with more information about the failure.
...that is it. I do not know what do do next, whether its the empty cells that is the issue (and if so, what do I need to do), or maybe the expression is incorrect. Would love some help, and happy to provide more info if needed. Apart from the date column I'm in good shape.
Thank you.
Regards,
D.
May 15, 2017 at 6:43 am
I am assuming that your formula is attempting to avoid processing 'empty cells'? If so, why are you checking OnTime for a length of one, rather than zero?
To check whether the empty cells are definitely your problem, try filling them with dates and doing the import again (on a test instance, of course).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 15, 2017 at 7:18 am
Also, if the cell is empty, the SSIS would import a NULL value. Why not also use the ISNULL operator to check if it's empty?ISNULL(OnTime) : NULL(DT_DBTIMESTAMP) ? {False statement}
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 15, 2017 at 8:29 pm
Hello,
Thank you for taking the time to read and answer my issue.
Phil,
I have tried filling in the empty cells, but it ended up not making a difference. Where you say I am checking for a length of one, rather than zero, I'm not sure where in the expression that part is. As I say I am totally new to this and got that expression from a site where someone had the same issue (they never came back to say whether it worked or not, it was a dead end).
Thom,
I did try the isnull, but I could not work out how to add it on to the start of my expression, I only ever get red text telling me it was wrong. Do I need to have something in between your suggestion and my expression to make them work together?
Regards,
D.
May 16, 2017 at 2:05 am
Duran - Monday, May 15, 2017 8:29 PMThom,
I did try the isnull, but I could not work out how to add it on to the start of my expression, I only ever get red text telling me it was wrong. Do I need to have something in between your suggestion and my expression to make them work together?
It might be that SSIS actually expects a full expression, rather than a formula that results in TRUE/FALSE. Some languages are fine without the second half of the expression (it's been a little while since I've used the SSIS expression tasks and i can't recall if it's happen about it or not). Does this work?ISNULL(OnTime) == TRUE : NULL(DT_DBTIMESTAMP) ? {False statement}
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 16, 2017 at 6:49 am
Duran - Monday, May 15, 2017 8:29 PMHello,Thank you for taking the time to read and answer my issue.
Phil,
I have tried filling in the empty cells, but it ended up not making a difference. Where you say I am checking for a length of one, rather than zero, I'm not sure where in the expression that part is. As I say I am totally new to this and got that expression from a site where someone had the same issue (they never came back to say whether it worked or not, it was a dead end).Thom,
I did try the isnull, but I could not work out how to add it on to the start of my expression, I only ever get red text telling me it was wrong. Do I need to have something in between your suggestion and my expression to make them work together?Regards,
D.
How is the date column formatted in Excel? What is its display format?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2017 at 7:31 am
Here is an example of the structure of the ISNULL in SSIS: ISNULL(ha_charges) || ha_charges == " " || ha_charges == "" ? "0" : ha_charges
which can be interpreted as (if ha_charges is null or if ha_charges equals a blank space or if ha_charges equals null then pass a 0) otherwise pass ha_charges.
Here is an example of something similar with a date field
LTRIM(ha_service_date) == "" ? "1/1/1900" : ha_service_date
Excel can be a bit tricky with dates. This is basically saying If ha_service_date equals null then pass 1/1/1900 otherwise pass ha_service_date.
May 17, 2017 at 8:27 pm
Hello,
Again, I appreciated all the help, its awesome!
Phil, I'm glad you asked as I was going to check I was using the right datatype, the dates in excel are seen as...
21/10/2017 11:16:02 AM or for earlier on in the day and month, 4/08/2017 9:54:15 AM (I'm wondering of locale is an issue here, the date at source is Australian, the destination is U.S.)
...which I think is DT_DBDATETIME2, but I'm not sure.
Alice, welcome to the thread. I will try your suggestion, based on my answer to Phil's question, does your suggestion 'fit' so to speak?
Thom, thank you, I'll try it out and see what happens.
Question: If using a Data Conversion Task to go from Nvarchar to varchar, and a Derived Column for converting dates, does it matter which way round they go?
If the derived column goes first, do I just un-select the columns getting converted by the derived column in the Data Conversion task, and have/do the rows flow through the Data Conversion task untouched?
Another issue has cropped up, but I'll hold off for a moment. One think I have noticed with SSIS is that trying to solve more that one issue at a time causes more problems!
Regards,
D.
May 18, 2017 at 5:47 am
Duran - Wednesday, May 17, 2017 8:27 PMHello,Again, I appreciated all the help, its awesome!
Phil, I'm glad you asked as I was going to check I was using the right datatype, the dates in excel are seen as...
21/10/2017 11:16:02 AM or for earlier on in the day and month, 4/08/2017 9:54:15 AM (I'm wondering of locale is an issue here, the date at source is Australian, the destination is U.S.)
...which I think is DT_DBDATETIME2, but I'm not sure.
Alice, welcome to the thread. I will try your suggestion, based on my answer to Phil's question, does your suggestion 'fit' so to speak?
Thom, thank you, I'll try it out and see what happens.
Question: If using a Data Conversion Task to go from Nvarchar to varchar, and a Derived Column for converting dates, does it matter which way round they go?
If the derived column goes first, do I just un-select the columns getting converted by the derived column in the Data Conversion task, and have/do the rows flow through the Data Conversion task untouched?Another issue has cropped up, but I'll hold off for a moment. One think I have noticed with SSIS is that trying to solve more that one issue at a time causes more problems!
Regards,
D.
OK, but I meant the cell format in Excel. Does it say 'Date' or 'General' or 'Text' or something else? This does matter: SSIS (or rather the ACE driver) takes notice of Excel display formats when deciding how to present the data to SSIS & knowing what this is informs what you do in SSIS. But it sounds like Date.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 21, 2017 at 5:17 pm
Sorry Phil, I see what you mean. Its formatted as General, but I had tried to format it as Date. I have just changed it to Date (14/03/2001), I guess I will have to account for the time somehow. Do you think it would be a betting idea to strip the time out, and perhaps put it into another column? I'd rather not do that if possible, but if its the better way I'd go with it.
Regards,
D.
May 22, 2017 at 1:42 pm
It does sound as if locale might be the issue (DMY vs MDY).
Just to be clear, please confirm the following:
a) The source is a spreadsheet with a date formatted in Australian format.
b) The target is a SQL Server 2012 datetime column
c) The import is running on the 2012 instance, which has a US locale.
If that is the case, as a test you could try opening up Excel and applying a custom date format to the dates in the spreadsheet (something like m/d/yyyy). Does that import?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2017 at 8:01 pm
Hey Phil,
I just wanted to say thanks for the help, and let you know what I did in the end. I messed about with the formatting of the Excel file, but this turned out to be a waste of time, as the file would eventually get replaced with another. I did play with the date and had a successful entry into the DB table, but lost the time (it was there but they were all 00:00:00), so in the end I imported them as text and this works for what I need it for which is just to report on. And the date comes through in US format, the DB table is set to US locale.
There did not seem to be a datatype for dd/mm/yyyy hh:mm:ss A/PM, nothing seemed to fit or work.
Now I need to make sure that before updates are made on rows with new information, the old one gets moved to an archive table, to compare between an Excel source, and database table, should I use a lookup transform or conditional split? I think need to use a lookup transform. The existing DB rows would be archived, the new ones will replace the old row on the DB table with new info.
Regards,
D.
June 13, 2017 at 7:04 am
Duran - Monday, June 12, 2017 8:01 PMHey Phil,I just wanted to say thanks for the help, and let you know what I did in the end. I messed about with the formatting of the Excel file, but this turned out to be a waste of time, as the file would eventually get replaced with another. I did play with the date and had a successful entry into the DB table, but lost the time (it was there but they were all 00:00:00), so in the end I imported them as text and this works for what I need it for which is just to report on. And the date comes through in US format, the DB table is set to US locale.
There did not seem to be a datatype for dd/mm/yyyy hh:mm:ss A/PM, nothing seemed to fit or work.
Now I need to make sure that before updates are made on rows with new information, the old one gets moved to an archive table, to compare between an Excel source, and database table, should I use a lookup transform or conditional split? I think need to use a lookup transform. The existing DB rows would be archived, the new ones will replace the old row on the DB table with new info.
Regards,
D.
I'd suggest importing your Excel data to a staging table and using standard T-SQL to do all of your INSERTs, UPDATEs and archiving (I'd suggest using UPDATE with an OUTPUT clause to do this).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply