SSIS Help..

Question

Post reply

SSIS Help..

rocky_498

SSCertifiable

Points: 6532
More actions
October 24, 2014 at 11:55 am

#291854

Hi Guys,
Need help in SSIS.
Here is source looks like
Sample Source Data .xlxs file
Add, New, Pro, Len, Source
Acorn,Null,Null,Null,Null
Null,FC,Null,Null,Null
Null,Null,Day,Null,Null
Null,Null,Null,2:00,Null
Null,Null,Null,Null,Phone
Null,Null,Null,Null,Web
Boster,Null,Null,Null,Null
Null,LC,Null,Null,Null
Null,Null,Night,Null,Null
Null,Null,Null,1:00,Null
Null,Null,Null,Null,Phone
Null,Null,Null,Null,Web
Here what I want Two Files from above One File
File One
Add, New, Pro, Len, Source
Acorn,FC,Day,2:00,Phone
Acron,FC,Day,2:00,Web
File Second
Add, New, Pro, Len, Source
Boster,LC,Night,1:00,Phone
Boster,LC,Night,1:00,Web
Please any advise would be great appreciate.
Thank You.

Viewing 7 posts - 1 through 6 (of 6 total)

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

g.britton SSChampion Points: 13863 More actions · Answer 1

In the Dataflow, use a multi-cast transformation. Then, define the two destinations and select the columns you want.

Gerald Britton, Pluralsight courses

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thanks for your help, got it I can use multicast for different file, how about ?

Source:-

Add, New, Pro, Len, Source

Acorn,Null,Null,Null,Null

Null,FC,Null,Null,Null

Null,Null,Day,Null,Null

Null,Null,Null,2:00,Null

Null,Null,Null,Null,Phone

Null,Null,Null,Null,Web

Destination File:-

Add, New, Pro, Len, Source

Acorn,FC,Day,2:00,Phone

Acron,FC,Day,2:00,Web

this format ?

g.britton SSChampion Points: 13863 More actions · Answer 3

g.britton

SSChampion

Points: 13863

October 24, 2014 at 2:42 pm

#1754736

Sure. Try it!

Gerald Britton, Pluralsight courses

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

rocky_498 (10/24/2014)
Thanks for your help, got it I can use multicast for different file, how about ?
Source:-
Add, New, Pro, Len, Source
Acorn,Null,Null,Null,Null
Null,FC,Null,Null,Null
Null,Null,Day,Null,Null
Null,Null,Null,2:00,Null
Null,Null,Null,Null,Phone
Null,Null,Null,Null,Web
Destination File:-
Add, New, Pro, Len, Source
Acorn,FC,Day,2:00,Phone
Acron,FC,Day,2:00,Web
this format ?

To be honest, this is death by SQL because there is nothing in any of the rows to associate the correct rows with each other. Also, the "angular" nature of the data is typically caused by someone using an EAV as a source and not having a clue on how to do a proper CROSS TAB or PIVOT>

My recommendation would be to save yourself a whole lot of future heartache and tell the folks that are providing the data that there format is NOT acceptable especially from a data integrity stand point and that they REALLY need to spend 10 minutes learning how to create this simple output correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 5

Thanks for reply. I wish I can ask my client to provide different file. Is there any I can get the output that I need? through t-sql or SSIS?

Thank You.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

To get the desired output requires some data massaging, here is an example

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA([Add], [New], [Pro], [Len], [Source]) AS

(

SELECT 'Acorn', Null, Null, Null, Null UNION ALL

SELECT Null, 'FC', Null, Null, Null UNION ALL

SELECT Null, Null, 'Day', Null, Null UNION ALL

SELECT Null, Null, Null, '2:00', Null UNION ALL

SELECT Null, Null, Null, Null, 'Phone' UNION ALL

SELECT Null, Null, Null, Null, 'Web' UNION ALL

SELECT 'Boster',Null, Null, Null, Null UNION ALL

SELECT Null, 'LC', Null, Null, Null UNION ALL

SELECT Null, Null, 'Night',Null, Null UNION ALL

SELECT Null, Null, Null, '1:00', Null UNION ALL

SELECT Null, Null, Null, Null, 'Phone' UNION ALL

SELECT Null, Null, Null, Null, 'Web'

)

,ORDERED_SET AS

(

SELECT

SD.[Add] AS SD_Add

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SD_RID

,SD.[New] AS SD_New

,SD.[Pro] AS SD_Pro

,SD.[Len] AS SD_Len

,SD.[Source] AS SD_Source

FROM SAMPLE_DATA SD

)

,GROUPS_IN_SET AS

(

SELECT

OS.SD_Add

,ROW_NUMBER() OVER (ORDER BY OS.SD_RID) AS GR_RID

,OS.SD_RID

FROM ORDERED_SET OS

WHERE OS.SD_Add IS NOT NULL

)

,MARKED_GROUPS AS

(

SELECT

GIS.SD_Add AS GROUP_NAME

,GIS.GR_RID AS GROUP_NUMBER

,GIS.SD_RID AS GROUP_START

,ISNULL(GTO.SD_RID - 1,(SELECT MAX(SD_RID) FROM ORDERED_SET)) AS GROUP_END

FROM GROUPS_IN_SET GIS

LEFT OUTER JOIN GROUPS_IN_SET GTO

ON GIS.GR_RID = GTO.GR_RID -1

)

,SUB_GROUPS AS

(

SELECT

MG.GROUP_NAME

,OS.SD_Source

FROM ORDERED_SET OS

CROSS JOIN MARKED_GROUPS MG

WHERE OS.SD_RID BETWEEN MG.GROUP_START AND MG.GROUP_END

AND OS.SD_Source IS NOT NULL

)

SELECT

MG.GROUP_NAME

,MG.GROUP_NUMBER

,SG.SD_Source

,MAX(OS.SD_New ) AS SD_New

,MAX(OS.SD_Pro ) AS SD_Pro

,MAX(OS.SD_Len ) AS SD_Len

FROM ORDERED_SET OS

CROSS JOIN MARKED_GROUPS MG

INNER JOIN SUB_GROUPS SG

ON MG.GROUP_NAME = SG.GROUP_NAME

WHERE OS.SD_RID BETWEEN MG.GROUP_START AND MG.GROUP_END

AND OS.SD_Source IS NULL

GROUP BY MG.GROUP_NAME,MG.GROUP_NUMBER,SG.SD_Source;

Results

GROUP_NAME GROUP_NUMBER SD_Source SD_New SD_Pro SD_Len

---------- -------------------- --------- ------ ------ ------

Acorn 1 Phone FC Day 2:00

Acorn 1 Web FC Day 2:00

Boster 2 Phone LC Night 1:00

Boster 2 Web LC Night 1:00