CODEWITHSUNDEEP
sql-serverssis
Rajiv
Rajiv

Reputation: 685

ssis convert data from source to destination

I have the below data in an excel source:

Date: 1-Jun-15
Received: 10
Answered: 9
AvgWaitTime: 0:00:05
AvgHandleTime: 0:00:07
Abandoned: 1

The destination table is as below:

Date date,
Received float,
Answered float,
AvgWaitTime time(7),
AvgHandleTime time(7),
Abandoned float

When I am trying to import the data I am getting error in Input column date as sql is unable to convert it to date format. I surpassed this by creating a new table as per the suggestion given by sql. In the new table the data type for the date column is datetime. Then I got stuck in AvgWaitTime as is sql is unable to convert it to time format.

Is there a way to convert all the required column data from source and then put it to destination? Thanks in advance.

Upvotes: 0

Views: 467

Answers (1)

V2k
V2k

Reputation: 354

In the destination table you could change the type of the column date to string. It should work. Then you could use a derrived column placed in your task between the source and destination to change it back to date (type datetime, derrived column tab use replace). Untested code below:

> (DT_DATE)("20" + 
>     SUBSTRING([ReceivedDt], 1, 2)  + "-" + 
>     SUBSTRING([ReceivedDt], 3, 2)  + "-" +
>     SUBSTRING([ReceivedDt], 5, 2))

Upvotes: 1

Related Questions