S2.2.1 - SSIS Tutorial - Setting up UK Address Validation

Note: if you don't have UK addressing, then we suggest you skip ahead to here.

Otherwise let's get started by mapping the data source and inputs.

1) Launch SSDT, first create a new integration services project, then drop a corrected address 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) To edit the task, double click on the task, or right click and choose edit

4) choose the UK template or your own template

5) click the 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

it’s 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. If you don't have one, matchIT SQL can make one for you (it will be an int type column called matchIT_SQL_Auto_ID that starts at 1)

8)

go back to SSDT

choose the table, and choose the ID, mention with a prompt that if your table doesn’t already have an ID (but it should), you can otherwise choose AUTO

9) click next, and map the inputs, the most common table layout is company, 4-5 address lines and a postcode

in this example you would map these to Organization, Address 1 - 4 and Postcode

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

10) What if my data isn’t like the common standard above?

a) If you have a name field, such as fullname or first/lastname, these don’t get mapped to the addressing task unless you have the additional Names Dataset. If you don't have the Names Dataset but you do have name fields that you want to use later in the matching process, don’t worry - we will cover that in a later section)

b)if you don’t have a company, that’s ok – you can choose to add company to the outputs (covered in the next section) or ignore companies entirely.

  c) What if I only have a single address line?

  in this case you would map your only address line as Address 1, if your single address line does not also contain the postcode then you should map the postcode field