question

MickiArruda-2207 avatar image
0 Votes"
MickiArruda-2207 asked MickiArruda-2207 answered

convert a text field(dt_str) to a time7 datatype in sql 2014

In a SSIS package, how do I convert an import a text field(dt_str) to a time7 datatype in sql 2014. In other data flows, a script component was added and then in the script editor we would write code like this example

row.dfas = convertdatestring(row.column9)

this data flow was created years ago. Editing it is okay, but now I am creating another data flow from scratch in VS 2017 with SSDT and that code does not work. the only command close is convert, and it does not tell me how to write the rest.
the dt_str coming is in in a number format of 6 characters. It needs to go into SQL 2014 as time. It is supposedly in 24 hr time format

Any help is appreciated

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
1 Vote"
Monalv-msft answered JeffreyWilliams-3310 commented

Hi @MickiArruda-2207,

We can use Derived Column Transformation to convert the datatype from DT_STR to DBTIME2 in ssis package.

Please refer to the following expression and pictures:

(DT_DBTIME2,7)(SUBSTRING(Date,1,2) + ":" + SUBSTRING(Date,3,2) + ":" + SUBSTRING(Date,5,2))

87976-df.png
87977-derivedcolumntransformation.png

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




df.png (89.3 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My out put format is not the same as yours. in my sql table it is coming over with it putting todays date in front of the time, when only time is desired. Can you tell me what I am doing wrong? I have tried, date, timestamp, time(7) etc. in the sql table.
This is my code. (DT_DBTIME2,7)(SUBSTRING(TMLMOD,1,2) + ":" + SUBSTRING(TMLMOD,3,2) + ":" + SUBSTRING(TMLMOD,5,2))


This is the results 2021-04-19 07:01:34.000 converted from 070134

0 Votes 0 ·

The column data type in SQL Server is set to datetime - change the columns data type to time(7) and the value in the table will be what you expect.

1 Vote 1 ·

This works now. Can you tell me how to get it to not error out with Nulls though. If all the data has values, it works good, when I put the whole file in (which has nulls sometimes), it fails.

0 Votes 0 ·
Show more comments

Hi @MickiArruda-2207,

I need to convert column 9 to a time, column 8 to a date.

Could you please share the example data in your column 8 and column 9?

Best regards,
Mona

0 Votes 0 ·
MickiArruda-2207 avatar image
0 Votes"
MickiArruda-2207 answered

So I tried this and it does not work.
(TMLMOD1 == "") ? NULL(DT_DBTIME2,7) : (DT_DBTIME2,7)(SUBSTRING(TMLMOD1,1,2) + ":" + SUBSTRING(TMLMOD1,3,2) + ":" + SUBSTRING(TMLMOD1,5,2))

I get the error below. I made sure one row had a null value in that field. Nulls on source and destination have been checked and unchecked to see if it would work, but it doesn't. as soon as I enter data in that field, it works again.


[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.Outputs[Derived Column Output].Columns[TMLMOD]" 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.