How to insert column wise data as a seperate rows using ssis
Hi All, I have my data in excel like this .... SNO LOAN ID CM ACM RCM 1 RLBLAHM000008381 PRATHNA SPECIALITES CHANDRA MOLI VIRAT D SHUKLA 2 RLBLAHM000008777 RADIANT MEDIA CONVERGENCE BHUPESH SHRIVASTAVA VIRAT D SHUKLA 3 RLBLAHM000008812 DHARINI MARKETING PVT LTD BHUPESH SHRIVASTAVA VIRAT D SHUKLA 4 RLBLAHM000009293 KULDEEP STEEL SYNDICATE BHUPESH SHRIVASTAVA VIRAT D SHUKLA 5 RLBLAHM000009319 JSH ENTERPRISE CHANDRA MOLI VIRAT D SHUKLA 6 RLBLAHM000009376 PUNJAB MOTOR HOUSE BHUPESH SHRIVASTAVA VIRAT D SHUKLA And for each Loan ID I have to insert all the roles...My roles are (CM,ACM,RCM -- Headers) My ouput will look like this : LOAN ID Roles RLBLAHM000008381 PRATHNA SPECIALITES CHANDRA MOLI VIRAT D SHUKLA RLBLAHM000008777 RADIANT MEDIA CONVERGENCE BHUPESH SHRIVASTAVA VIRAT D SHUKLA Can any one guide me how to achieve this using SSIS package... (For each loan id CM,ACM,RCM should be inserted as a seperate record) My source is -- excel and my destination is SQL Server. Appreciate ur help. Thanks, vineesh1701
October 27th, 2010 9:37am

Just to clarify, which if the following outputs do you want? should the roles be concatenated into a single column should the roles be on seperate rows with the loan id duplicated should there be 2 seperate tables with a relationship (ie LOANID & ASSOCIATEDROLES) ThanksNuke the site from orbit, its the only way to be sure!
Free Windows Admin Tool Kit Click here and download it now
October 27th, 2010 9:55am

The below will perform what you want assuming you want the [LOAD ID] to repeat for each row SELECT myTable.[LOAD ID] ,myPivot.Roles FROM myTable UNPIVOT (Roles FOR RolesType IN (CM,ACM,RCM)) AS myPivot my blog http://karlberan.wordpress.com/
October 27th, 2010 10:03am

Hi Karl, Thank you for your response. I have a small clarification.... My input is --- LOAN ID CM ACM RCM RLBLAHM000008381 PRATHNA SPECIALITES CHANDRA MOLI VIRAT D SHUKLA RLBLAHM000008777 BHUPESH SHRIVASTAVA BHUPESH VIRAT D SHUKLA and My output should be like this : LOAN ID Role RLBLAHM000008381 PRATHNA SPECIALITES RLBLAHM000008381 CHANDRA MOLI RLBLAHM000008381 VIRAT D SHUKLA RLBLAHM000008777 BHUPESH SHRIVASTAVA RLBLAHM000008777 BHUPESH RLBLAHM000008777 VIRAT D SHUKLA Can you let me know ...how to achieve this result..? vineesh1701
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 3:11am

HI I need the second one..see above one for more details. Thanks, vineesh1701
October 28th, 2010 3:12am

Youneed the Unpivot transformation. See here for an example: http://www.bimonkey.com/2009/07/the-unpivot-transformation/James Beresford @ www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 4:22am

create table #t(loan_id varchar(50),CM varchar(50), ACM varchar(50),RCM varchar(50)) insert into #t values ('RLBLAHM000008381', 'PRATHNA SPECIALITES' ,'CHANDRA MOLI' ,'VIRAT D SHUKLA' ) insert into #t values ('RLBLAHM000008777', 'BHUPESH SHRIVASTAVA' ,'BHUPESH' ,'VIRAT D SHUKLA' ) SELECT loan_id, RoleQuantity AS [Role] FROM #t UNPIVOT (RoleQuantity FOR RoleNumber IN (CM,ACM,RCM)) AS cBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
October 28th, 2010 5:12am

It was a great help . Thank you. One more thing if RCM is not there or if RCM is Null then I do not want that entire row with that loan id. For example : This is my input data : LOAN ID Name of CM Name of ACM Name of RCM ------------------------------------------------------------------ RLBLAHM000008381 ,PRATHNA SPECIALITES, CHANDRA MOLI, VIRAT D SHUKLA RLBLAHM000008777 ,BHUPESH SHRIVASTAVA ,BHUPESH, ' ' Above RCM value is Null for this loanid - RLBLAHM000008777 ..In this case again this loanid should not appear with null RCM value. Now my output should be like this : LOAN ID ROLE ---------------------------------------- RLBLAHM000008381 PRATHNA SPECIALITES RLBLAHM000008381 CHANDRA MOLI RLBLAHM000008381 VIRAT D SHUKLA RLBLAHM000008777 BHUPESH SHRIVASTAVA RLBLAHM000008777 BHUPESH only 2 records for this RLBLAHM000008777 loan as RCM is null. Can you pls help me how to achieve this ..? Hope I made this clear . Appreciate ur help . Thanks ,vineesh1701
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 9:43am

Use a conditional split after the unpivot to direct only the rows you want to your target. Example package: http://www.bimonkey.com/2009/07/the-unpivot-transformation/ James Beresford @ www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
November 1st, 2010 6:07pm

It should work See if that helps you create table #t(loan_id varchar(50),CM varchar(50), ACM varchar(50),RCM varchar(50)) insert into #t values ('RLBLAHM000008381', 'PRATHNA SPECIALITES' ,'CHANDRA MOLI' ,'VIRAT D SHUKLA' ) insert into #t values ('RLBLAHM000008777', 'BHUPESH SHRIVASTAVA' ,'BHUPESH' ,NULL ) SELECT loan_id, RoleQuantity AS [Role] FROM #t UNPIVOT (RoleQuantity FOR RoleNumber IN (CM,ACM,RCM)) AS c If it does not please sample data in format as I did and show the desired outputBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2010 2:13am

Thank you very much. I got it. vineesh1701
November 2nd, 2010 9:27am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics