S2.3.2 - SSIS Tutorial - Setting up Key Generation

*skip to step 3 if you just set up a template in the previous article.

1) Launch SSDT, first create a new integration services project, then drop a key generation task onto the package
 

2) rename the task as a best practice, when there are multiple tasks with similar names, it may be harder to keep track later

3) double click on the task, or right click and choose edit to open the task editor

4) Choose your template, ideally your own one discussed in the previous article

5) Choose ellipses to launch the connection browser

6) Select a server and database, click test connection, then click ok

7) go to SSMS and look at the table, its best to take a look and understand your data

It’s also a best practice that all your tables you point matchIT SQL to should have a non-nullable ID column with a primary key constraint against them.


 

8) go back to SSDT

Choose the table, and choose the ID, if you table doesn’t already have an ID (but ideally it should), you can otherwise choose AUTO, and it will create a column called matchIT_SQL_Auto_ID like in above screenshot

9)

click next, and map the inputs, a common table layout is fullname, company, address lines and the postcode, if your table isn’t like that, it’s OK, we can handle it however it comes in, but make sure to keep reading.

notice the fields on the left are from your source table, the fields on the right are the  API mappings

10) Then you would just click next, then save

Other common variations:

A) What if I ran address validation before, or have my addresses in a separate table

In that case, you would just connect the address validation task to key generation, then when you open up key generation, the connection would be read in from the previous task

You would set up your tables like below

on the inputs, you would use the address corrected table for any address inputs, you may need to scroll down to see the address table if your source has a lot of columns.

B) What If you have extra fields like a birth date, telephone, email, tax ID, invoice number that you may need to incorporate into matching or included in your output table?

There are already options for email, DOB, Telephone, any other fields would fall under a “custom Field”, you can map up to 9 custom fields. If you need them incorporated into the matching, then make sure to map them now.  You can always come back to the task and change mappings later.

C) Suppose you have a universe of data with a last modified date and you don't want to bother re-generating keys on that universe for records that haven't changed? Well you can do this by simply mapping your last modified field as type 'LastModifiedDate' in your input and then using the Incremental Key Generation option in the output tab.