The first thing to decide is whether to use stored procedures and config files or to create SSIS packages. (It is entirely possible to use a combination of the two, but for simplicity we recommend choosing one over the other.)
It is important to remember that both methods offer near identical functionality, with one or two additional stored procedures and triggers unavailable to SSIS packages. Using the stored procedures provides a greater level of flexibility that’s not possible when using SSIS components; however, creating an SSIS package offers a more visual and intuitive way of architecting a process.
Stored Procedures:
New configs and .sql scripts should be stored in the data folder.
You will then need to integrate the mSQL stored procedures into your process (one-off, manual, or even automatic and scheduled).
The stored procedures are normally automatically upgraded whenever mSQL is upgraded to a newer version. (In older versions of the product it was necessary to execute DropStoredProcedures.sql then CreateStoredProcedures.sql.)
SSIS Packages:
New SSIS packages should be created in the data folder.
Create a new SSIS package via BIDS or SSDT (File->New->Project->Business Intelligence->Integration Services Project). Drag relevant mSQL tasks from the Toolbox (or SSIS Toolbox) onto the Control Flow to produce your workflow, then connect and configure each task.
Components: SQL Server 2005 and 2008 users will need to manually add the mSQL SSIS components to the toolbox. With Visual Studio (BIDS) open, right-click the toolbox and select “Choose Items…”. When the dialog appears (after a delay), select the “SSIS Control Flow Items” tab, and then check all listed mSQL tasks.
Execution: SSIS packages can be executed from within BIDS or SSDT, from the command line via DTExec.exe, or by SQL Server Agent jobs.
Process Design:
Generally, you will want to generate match keys on any new data table, and perform deduplication of the new data or find its overlap against a master table. However, deduplication processes can be complex, so a detailed design is highly recommended. Please contact 360Science if you require additional help or advice.