Convert integer to date

Question

Convert integer to date

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

lukaszpiech SSCommitted Points: 1538 More actions · Answer 1

I've checked "Column names in the first data row" in a flat file connection manager, so that's not the issue:/

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I could be dumb, but when I look at your calculation and your input flat file, I think you are over-working the date are you not?
the date in the flat file is in the format of yyyy-mm-dd so why are you doing that substring calculation on it?

I took your data source (the flat file) and just loaded it up with no trouble. I changed the BirthDate column to be a Date and I had no issues.
You have a big red X on the Flat File source... are you sure it is unhappy with the BirthDate column?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

lukaszpiech - Wednesday, June 28, 2017 2:01 PM
Thanks for a quick replay!
Here are my thoughts:
1. I've tried to add new column and replace BirthDate column. Both methods failed.

2. I am working with SQL Server 2014 not 2012 like in the book
3. Package execution screen below:

4. Output message with errors

5. I've changed data type of BirthDate column in Flat File source to: Unicode string [DT_WSTR]
That's all what comes to my mind at the moment. Maybe you will notice something in attached screens.
P.S.
@bill Talada, thank you for replay also!

Check the source data.
Load the file without any conversions and look for non-dates entries in the column.
May be an empty string in some row(s), or something like "N/A".
It always makes sense to validate uploaded data before proceeding with its transformations.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Are you loading the last empty row from the file?

_____________
Code for TallyGenerator

lukaszpiech SSCommitted Points: 1538 More actions · Answer 5

Sorry for so late answer but i have a good reason to be quiet as a became a father for the second time on the 29.06.2017 🙂 Hurray!!!:)
So after this off-topic let me get back to the problem.
@bmg002
I've tried to load data without any transformations but package returns following error messages (to keep it short I will show only descriptions of errors, 4 errors to be exact, detailed errors are attached):
1. "Invalid character value for cast specification"
2. "The value could not be converted because of a potential loss of data."
3. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error
4. The ProcessInput method on component "OLE DB Destination" (39) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (52)

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [39]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
If you your package runs without errors that means that the source file isn't corrupeted, i guess.
@sergiy
Deleting last row from a source file didn't help. I have also deleted most of the data in flat file, leaving 25 of them, and i have deleted all the rows with empty or NULL value in the BirthDate column. Failure again.
I will try to solve this problem!
Thank you for answers, if you have any other ideas please let me know.
Regards
Lukas

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Congrats on being a father again! Hope everyone is well and you aren't too tired. Yet.

I did the same thing as BMG and just did a straight import without any problems. From the error and from what you have on your screen shot, I would guess you have data types mismatched - input and/or output of the columns for the text file and also with the table destination. I could get your errors if I changed the data types on my table as you can't go unicode to non-unicode. They defaulted to unicode for the file.
But you also may have some mismatches in the input and output columns for the text file. Right click on your flat file source and select Show Advanced Editor. On the Input and Output tab, check your data types for External columns and Output columns.

Sue

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Sue_H - Sunday, July 2, 2017 5:01 PM
Congrats on being a father again! Hope everyone is well and you aren't too tired. Yet.
I did the same thing as BMG and just did a straight import without any problems. From the error and from what you have on your screen shot, I would guess you have data types mismatched - input and/or output of the columns for the text file and also with the table destination. I could get your errors if I changed the data types on my table as you can't go unicode to non-unicode. They defaulted to unicode for the file.
But you also may have some mismatches in the input and output columns for the text file. Right click on your flat file source and select Show Advanced Editor. On the Input and Output tab, check your data types for External columns and Output columns.
Sue

Congratulations on being a father, second time around. If you aren't sleep-deprived yet, you will be soon. Then again, you've done this before.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions