mSQL - SSIS Tutorial - Setting up US Address Validation


Note: if you don't have US 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 US 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 mSQL to should have a non-nullable ID column with a primary key constraint against them. If you don't have one, mSQL can make one for you (it will be an int type column called matchIT_SQL_Auto_ID that starts at 1)




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, two address lines, city, state, zip

you would map these to company, street, street2, city, state, zip

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, such as fullname or first/last, that doesn’t get mapped to the addressing (don’t worry - we’ll use that later), or if you don’t have a company, that’s ok – we just may be able to append a suite to some info if you do give us a company
the rest would be
address1 = street
address2 = street2
address3 = city
address4 = state
zip = zipplus4



B. What if I have 3 address lines?

the 3rd one would go to urbanization

address1 = street
address2 = street2
address3 = urbanization
state = state
zip = zipplus4

C. What if my zip is split into two separate columns or only have a single address line?

in this case, zip and plus 4, instead of zipplus4, and if you don’t have 2 address lines, that’s ok, just map it to street

address1 = street
state = state
zip = zip
plus4 = plus4


D. What if I have city/state/zip all in one column?
then map it to city in this case, we’ll parse it out when we can validate it, but this could lower the validation rate

address1 = street
address2 = street2


E. What if I store my street address in separate parts?

this is currently not something we support on input (we can output it like this for you if you need) but we recommend concatenating the columns in this case before giving the data to mSQL