I want to take two business key columns in scd transformation in ssis

Question

Hi all,

   This is Azarudhin.....

  I need a help that for our requirement we are generating the GUID  from the project server for each districts but in districts multiple places are there i need to implement the same GUID which had given to district to all the sub places in the district...

How can i achieve this... Plz anyone help me ASAP

Regards,

Azarudhin S

Answer 1

Can you tell us whether the Source data and target are in same server or in different server.

1. If it is in same server, then  you can use Merge SQL Statement to perform this operation. Below link helps you in understanding Merge statement in SQL

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

http://www.codeproject.com/Articles/37172/Merge-Statement-in-SQL-Server-2008

2. If it is in different you can accomplish using 2 different ways.

a. By exporting all your source data into staging table which is there in target database and then use method 1, which is faster.

b. by using SCD transformation you can achive this , but this impacts performace if you have lakhs of rows. Below link helps you in understanding SCD transformation.

http://msdn.microsoft.com/en-us/library/ms141715.aspx

Answer 2

Hi Naveen,

I am explaining you the correct requirement is i am having 18 columns in a excel sheet with production server i have to take that file and do some calculations and put in 2 different tables and maintain the history. How can i achieve this but these all will done in backend server......

But in that sheet a column named as GUID but it will be duplicating(same GUID for all locations in that state i mean a state having one GUID but the locations in the state is having the same GUID)  how can i split that 18 columns to 8 for one table and 14 columns to other table by adding calculations. 

How can i achieve this what is the best approach  i have to add a new identity column if i added that column and what basis i need to split that columns from the sheet.

Regards,

Azarudhin S

Answer 3

Hi Azarudhin,

What you can do is to

1. Transfer all your excel data into staging table, then perform the required calculations on top of the data which is present in staging table by writing stored procedure to perform the operations.
2. It is obvious that the locations in the same state share the same GUID.
3. To split the columns to different tables use Multicast so that you can redirect the required columns to multiple tables
4. if you have any data to be splitted based on some condition use Conditional Split.

Check this below links to know about Multicast and Conditional Split transformations.

https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-conditional-split/

http://msdn.microsoft.com/en-us/library/ms137886.aspx

http://msdn.microsoft.com/en-us/library/ms137701.aspx

http://www.mssqltips.com/sqlservertip/2047/ssis-multicast-transformation-vs-conditional-split-transformation/

Regards

Naveen