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 as well as the legacy UI is no longer supported. 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) Go through the datasource and review the XML, the settings should be mostly similar to the various SSIS tasks, just in a different order, although there are some options that are only exposed in the template – this is relevant to genkeys task, matching options is relevant to the findmatches, and other options are spread about.
13) Now goto the mSQL scripts directory
14) Open matchingprocess.sql (or overlapprocess.sql if matching across two datasources)
15) Modify the sql to have the right configuration xml, and grouping level
16) Execute the xml
17) 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.