mSQL - SSIS Tutorial - moving a matching process from SSIS to Stored Procedures

So you set up your project in SSIS, but you find out that you actually need to make use of the stored procedures instead.  There’s no need to start from scratch, if you’re already used to SSIS, then the mSQL UI for editing xml’s may seem a little clunky to you.  Its been a growing trend among clients to build their process in SSIS regardless, then take the xml that the SSIS project produces the first time you run it and go from there.

 

So first question – when you installed, did you choose SSIS only, or both?  If you chose both, skip ahead to step 4, otherwise if you’re unsure or chose SSIS only - pay attention to below.

 

1) You can check if you have the matchIT SQL database with the assembly and stored procedures from SSMS – if you have a matchIT_SQL database that looks like this, you’re good.

 

 

2) If not? Then navigate to your mSQL install bin directory

normally: C:\Program Files\mSQL\bin

3) Locate matchITSQLconfigurator.exe and run it, choose both, and then make sure you have the right server, then connect


click to create the database containing the stored procedures

once successful – exit

*Alternatively, we provide sample scripts that allow you to drop (if previously made) and create the stored procedures. If you don't want to use our default name of 'matchIT_SQL', then you'll need to change the use statement at the top of those scripts.

You'll find the sample scripts in:

C:\mSQL\scripts

DropStoredProcedures.sql  and
CreateStoredProcedures.sql

When upgrading/reinstalling your mSQL Version you'll want to drop and recreate the assembly/SP's so you don't end up with version mismatch errors.

 

4) Now back to switching to stored procedures

first of all, we’re assuming you’ve already run your SSIS process in full at least once since you’ve made any changes to it. If not, run it now

 

5) Next lets goto the mSQL temp directory

 

6) Locate the folder with your project – its going to have a GUID type name to it

 


 

7) Inside that folder, locate the index.xml and open that up



locate the name of last task that was run – normally groupmatches or groupoverlap

 

8) Find that corresponding xml and copy it to your mSQL config directory, normally C:\mSQL\config

 

9) Rename that xml to something more relevant

 

10) Now open that xml up and search for connectionstring

 

11) Make the data source ID something more simple – such as 1, and in the connection string where it says =true,  change it to  SSPI;

 


 

12) Launch the UI and open that XML, (login and pass are admin/admin by default)

 

13) Go through the datasource – this is relevant to genkeys task, matching options is relevant to the findmatches, and other options are spread about

 

14) Close the UI

 

15) Now goto the mSQL scripts directory

 

16) Open matchingprocess.sql (or overlapprocess.sql if matching across two datasources)

 

17) Modify the sql to have the right configuration xml, and grouping level

 

18) Execute the xml

 

 

19) Congrats – you just used stored procedures

 

*For processes that include addressing, you will need to need to reference a separate XML for the addressing which you can find by referencing the index.xml

 

A) you can similarly copy that XML and update the xml's name and data source ID and run the addressing separately, or

 

B) you can grab the xml nodes from  <addressing> to </addressing>  and place it in the XML after the </fieldmappings>  node in the xml you grabbed from the groupmatches (if you're running an overlap and ran address validation on both data sources, you'll need to do this twice and make sure you align it with the correct data source)

 


Its not uncommon to just run addressing/NCOA through SSIS as a separate process from the matching as those are handled separately sometimes.