Help with importing Text file

Question

Help with importing Text file

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
March 18, 2014 at 9:08 am

#289432

Hi there,
I have a text file with the following data row -
"D0S","382",01/01/2014,11:15:43,"382",16966,"S",
In SSIS I have managed to import the following columns into the database - D0S and 382. But I'm struggling with 01/01/2014,11:15:43.
Within my connection manager I have a text qualifier of " and a column delimiter of , .
What this has done is spit the following into two columns - 01/01/2014,11:15:43 due to the comma existing between the date and time, but no " exists so it's not taking it as one column.
Therefore I thought I would use the "Derived Column" function to merge the two together. When I'm in the Derived Column Transformation Editor, I go to create it with the following expression -
[Transaction Date] +" " + [Transaction Time]. This appears in red and doesn't appear to be in the correct formate - what do I need to do to combine two fields in a text file?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What type are your [Transaction Date] & [Transaction Time] defined as? If they're not some kind of string, you might need to cast them before concatenating them.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Hi Luis,

Going to the Flat File Connection Manager Editor and the Advanced tab, I can see that it has split that field into two.

I renamed them Transaction Date and Transaction Time in here and have set the Transaction Date to - date [DT_DATE] and Transaction Time to database time [DT_DBTIME]. I'm not sure if that is correct?

What I want to do is create a field the merges/concatenates the two together and allow me to import into a database filed that will be set to datetime.

Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Try setting them as a String, because you can't add a dbtime to a date (don't ask me why, it's just the way they designed them). If you concatenate both strings, you can cast them into a datetime value in your derived column.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

Sorry Luis, I've now got the two string values concatenated - but how to I get them cast as a date in the expression?

Thanks for your perseverance with us.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

I can't test this because my BIDS is not working, but you should end up with something like this:

(DT_DBTIMESTAMP)[Transaction Date] +" " + [Transaction Time]

You could check for other possible date data types in here: http://technet.microsoft.com/en-us/library/ms141036.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 6

I'm really sorry but Concatenating those two fields and trying to change it to a DT_DBTIMESTAMP and I get the attached error. I have not idea what I'm doing wrong.

Thanks again for your help.

So in the source the date looks like - 01/01/2014,11:15:43

Due to the comma in between the date and time it splits it into two columns. I need to concatenate them and them convert them to a date time so that they can be imported into one field in the datebase that is a datetime.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 7

So - I've got it working with some help here -

This may be the long way round of doing it - but I've left the Transaction Date and Transaction Time as Substrings.

Then in SSIS by creating a Derived Column I used the following -

SUBSTRING([Transaction Date],7,4) + "-" + SUBSTRING([Transaction Date],4,2) + "-" + SUBSTRING([Transaction Date],1,2) + " " + [Transaction Time]

This then presented that date in the right format to enable it to be imported into a datetime field. - example - 2014-01-01 11:15:43.000

Luis thanks very much for you help.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

If that works for you, then great. I tried this: (DT_DBTIMESTAMP)([Transaction Date] +" " + [Transaction Time])

And worked fine, but it depends on your local settings for dates. Your way shouldn't have problems as it's an ISO compliant format.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2