Support DESK

Follow

S2.8.1 - SSIS Tutorial - moving a matching process from SSIS to Stored Procedures

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, if you’re already used to SSIS, then the matchIT SQL 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 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) 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 go to the matchIT SQL 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

*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 tab in the UI if you want to run addressing through the UI.

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

 

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.