Previous Article | matchIT SQL Index | Next Article |
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; it's 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 matchIT SQL install bin directory
normally: C:\Program Files\matchIT SQL\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:\matchIT SQL\scripts
DropStoredProcedures.sql and
CreateStoredProcedures.sql
When upgrading/reinstalling your matchIT SQL 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 go to the matchIT SQL 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 matchIT SQL config directory, normally C:\matchIT SQL\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 – this is relevant to genkeys task, matching options is relevant to the findmatches, and other options are spread about
13) Now go to the matchIT SQL 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
*as a note, this only works well for the matching portion. The addressing is separate as when the addressing is run in SSIS it creates its own separate XML with just the addressing related nodes, so you'll manually need to set up the addressing in the XML.
Its not uncommon to just run addressing through SSIS as a separate process from the matching as those are handled separately sometimes.
Previous Article | matchIT SQL Index | Next Article |