Run SSIS package from a job

Source DB: VM4ItaiTest, Exported Table/Process: TestTable

Data Warehouse (destination) DB: ItaiDW

1. Add a column IsExportToDW to table TestTable. (DEFAULT 0):

• 0 = new row, was not Exported yet to DWH.
• 2 = in export process.
• 1 = Exported to DWH.
• Create index for this column.

2. In the Stored-procedure that select the data for this process (that called, for this example dbo.ItaiFillTestTable), Select only records where TestTable.IsExportToDW = 2

3. Create new SSIS package TestTablePackage.dtsx:

• Source: EXEC [dbo].[spr_Itai_PowerData_FillTable_AssignmentsHistory] (in DB VM4ItaiTest).
• Destination: PowerData_AssignmentsHistory (in DB ItaiDW).

4. Create configuration file for the package.

• Edit Connection string manually.
• The package and the configuration file should be in the same folder.
• SSIS Package Configuration - http://copypastenet.blogspot.com/2011/02/ssis-package-configuration.html

5. Create a Job to export data to DWH ItaiTestDW_TestTable:

• Step 1: UPDATE TestTable SET IsExportToDW = 2 WHERE IsExportToDW = 0.
• Step 2: execute TestTablePackage.
• Step 3: UPDATE TestTable SET IsExportToDW = 1 WHERE IsExportToDW = 2.
• Schedule: Can be each X minutes.

Notes:

1. The package can be executed also from trigger on TestTable:

• Execute the package directly from the trigger (instead of Job and column IsExportToDW). Disadvantage: when the package will be failed – the trigger and also all the insertion will be failed too.
• Start the job from the trigger (don't wait to the job execution completion).

2. The package can be executed also from stored procedures.

3. In any process of the above – it's recommended to keep column IsExportToDW – in order to know if the process is failed and make it re-execute failed records.

4. To complete the solution, it's required to:

• Update column IsExportToDW (=0) when a new row is inserted (will be done automatically by default value 0 to the column).
• Update column IsExportToDW (=0) when a new row is updated.  Implement cases of row update in SSIS.
• Implement cases of row deletion in SSIS.

Problems (and solutions... (-: ) in process of running SSIS package from a job:
http://copypastenet.blogspot.com/2011/02/problems-in-process-of-running-ssis.html