I had to move the data from a central database to different databases (all databases with the same schema) and I wanted to achieve this trough parallel processing instead of sequential through a FOR loop
I created a parent package and added a sequence container in it .
I added a for loop inside the sequence container and added a call ( any number that I want to ruin in parallel ) to the child packages.
Created a parent package .
The Execute SQL Task: Update Processing List comes is used to reset the different databases active processing status once the data is moved.
Now I created a child package
( The child package is called the following way from the parent package using the Execute package Task)
Child package is the real place where everything happens.
I have a table where I have a list of clients ( databases) and the connection string that I read and put into a variable ( child package level) and use that to create the dynamic connection string to the multiple databases.
The Execute SQL task : Get Connection string into variables on the child package calls a stored procedure (proc_ResultProcessing_List) that returns the client code and the connection string to the databases and assigns to the variable varChildConnString and varClientCode.
Here is the code in the sp
-Section 1
Declare @ConnString varchar(100)
DEclare @clientCode int
--Section 2
BEGIN TRAN TRAN1
--Section 3
SET Rowcount 1
--Section 4
select @ConnString = Connstring, @ClientCodeCode = clientCode from clientsProcessList where ds_id = 2 and Dataprocessing = 2
-- 1 is active processing
--2 is ready
--print @ConnString + space(5) + cast(@hospCode as varchar)
--section 5
UPDATE clientsProcessList
WITH (TABLOCK)
SET Dataprocessing = 1
WHERE clientCode = @clientCode
and ds_id = 2 and Dataprocessing = 2
--section 6
SET Rowcount 0
--section 7
SELECT isnull(@ConnString,'empty') as ConnectionString, isnull(@HospCode,0) as ClientCode
--section 9
COMMIT TRAN TRAN1
END TRY
BEGIN CATCH
print 'error here'
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine;
print 'rollback'
ROLLBACK TRANSACTION TRAN1 ;
END CATCH
Section 1
The first thing that we do is declare our @ConnString and @clientCode
Section 2
Next we start a Begin Tran
1. NOTE: The reason for this is so that we can actually lock the table whilst this transaction is happening.
2. NOTE 2: This is so that when the SSIS Packages are running in parallel, we know for a fact that each package will only get a valid ConnString and clientCode when trying to get another value. If it tries whilst another SSIS Package is busy it will wait until the transaction has been completed.
Section 3
We then set the RowCount to 1, this is so that we will only get one row back.
1. NOTE: we are doing this because we only ever want to get back one ConnString and clientcode
Section 4
Next is our TSQL Select statement
1. Here we are getting any ConnString, clientcode from our table, where the DataProcessing is set to 2.( ready to process)
2. We are then putting this into our @ConnString variabl and @ClientCode
Section 5
Next we are updating our table using the TABLOCK hint so that we can once again lock the entire table, to ensure that for our specific clientcode row it gets updated from a 2 to 1.
1. NOTE: This then means that this data is and has been processed when the next package comes along looking for data to process.
Section 6
We then set the RowCount back to 0 (zero)
Section 7
Then we run a TSQL Select statement so that we can then pass our @ConnString and @clientCode
( is null value to pass if reached EOR which is later used in the EXIT LOOP precedence)
1. NOTE: This is because we are using the varChildConnString and varClientCode passing this into a variable in our Execute SQL Task.
2. NOTE 2: We also set the isNull to 0 (zero) so that when there are no more rows to process it defaults to zero, which is explained laterin the Exit Loop.
Section 8
Finally we are then committing our transaction.
Within our Execute SQL Task we also map the result set to our variable called:
We execute the Execute SQL Task: Execute SPs while there is a value in the variable ( while the sp is returning a row)
The last step is to set the parent variable varLoopQuitParent = 1 in the EXIT loop by binding the parent variable in the child package .
Click on this LINK on how to bind (Passing Values To and From Child Packages)
The script for the EXIT LOOP
Make the varLoopQuitParent as ReadWriteVaroable.
