Wsrtjtyk

Need to convert data from the below format

From

1,Delivery Confirmation Email

5,11/09/2018 07:10

7,OOLU1939921

9,S00224239

To

Event: Delivery Confirmation Email;

Date: 11/09/2018 07:10;

Container: OOLU1939921;

Job: S00224239

or in a table format as

Event                          Date              Container        Job         

Delivery Confirmation Email    11/09/2018 07:10  OOLU1939921      S00224239

Is it possible through pivot in ssis?

Here is a similar question with answers suggesting a script task or an ELT approach

SSIS transformation (almost like a pivot)

You can try and implement the script transformation if you wish. Personally I prefer an ELT approach

1. Create a staging table with three columns:

.

CREATE TABLE DeliveryConfirmationStaging

(

ID INT NOT NULL,

StringValue VARCHAR(50) NOT NULL,

DTInserted DATETIME2(0) DEFAULT(GETDATE())

)

2. Load your text file into that table with a CSV source and an ADO SQL Server destination in a data flow.




3. Add a SQL script before your data flow with TRUNCATE TABLE DeliveryConfirmationStaging; in it




4. Lastly add this SQL script after your data flow which transforms it with SQL (borrowing @GordonLinoff answer)

.

insert into yourfinaltable (event, date,container,job)

select max(case when col1 = 1 then col2 end) as event,

       TRY_CAST(max(case when col1 = 5 then col2 end) as datetime) as [date],

       max(case when col1 = 7 then col2 end) as container,

       max(case when col1 = 9 then col2 end) as job

from DeliveryConfirmationStaging;

You don't mention what date format this is. Is it September 11th or 9th November?

I think you can use conditional aggregation:

select max(case when col1 = 1 then col2 end) as event,

       max(case when col1 = 5 then col2 end) as date,

       max(case when col1 = 7 then col2 end) as container,

       max(case when col1 = 9 then col2 end) as job

from t;

Fixed the problem through Pivot transformation in ssis.