tag:blogger.com,1999:blog-2303058199815958946.post8175591980188931269..comments2024-03-28T10:36:35.016+01:00Comments on Microsoft SQL Server Integration Services: Execute multiple child packages in parallel with loopJoost van Rossumhttp://www.blogger.com/profile/01125981589974671317noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2303058199815958946.post-86825551482790310962015-12-06T21:08:54.328+01:002015-12-06T21:08:54.328+01:00If each package has a different set of parameter t...If each package has a different set of parameter that are filled by the parent package then this trick doesn't work. You could try the <a href="http://microsoft-ssis.blogspot.nl/2014/06/ssis-2012-execute-package-task-external.html" rel="nofollow">script task</a> for that.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-89174137100041795832015-12-03T15:14:49.605+01:002015-12-03T15:14:49.605+01:00Very good approach. But what would you use instead...Very good approach. But what would you use instead of package caller task, if the packages that should be executed have different list of paramters?<br />I am working now on a SSIS packge to implemet this approach for a DW load, but since the packages have different list of parameters, I can't use the same package Caller task. I am thinking of using a script Task componenet, and call the packages using .Net Code. Do you have a better approach?Anonymoushttps://www.blogger.com/profile/17849494554889128276noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-49148649764390441732014-08-11T16:54:49.346+02:002014-08-11T16:54:49.346+02:00This is because I want to delete the first item/pa...This is because I want to delete the first item/package from the queue, but also get its name so I can use it to execute the package. But there are probably more solutions to accomplish this (all roads lead to Rome). My queue table (only containing a packagename) slowly empties and the parallel loops stop when it's completely empty.<br /><br />In the <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql" rel="nofollow">Transact SQL forum on MSDN</a> you ask the TSQL gurus your TSQL related questions.<br />Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-55936448893852029312014-08-09T20:45:25.205+02:002014-08-09T20:45:25.205+02:00Nice Post Joost. I'm working on something simi...Nice Post Joost. I'm working on something similar to this. I have very limited working knowledge of SSIS So I have few questions. Please Advise<br /><br />1. Why are you deleting the records from the CTE when you can delete the record from the actual FIFO Queue table.?<br /><br />2. I have a similar requirement , but i keep the stored procedure commands in the Queue table instead of package details. There is only a single package that polls the Queue to execute the stored procedure and extracts the results into a file.<br /><br />3. Lets say i do not use Parallel For Each Loop containers. Instead, i create a master package and use four parallel execute package tasks. Now i have 8 records that are in Queue and each has a priority. The Package begins executing the first four as per priority. What if , the 3 of them complete and the fourth one is still processing. It would not allow the other 4 that are in Queue untill the fourth one is complete. So i would need to execute multiple copies of the master package using SQL Jobs so that all the records in queue get executed in parallel until the Queue is empty.<br /><br />4. Finally , im scheduling four jobs simultaneously at the same time , if i use rowlock,readpast hints on the Queue table then will it ensure the same record is not picked by more than one job? <br /><br />i'm using a code as below. Here , i'm updating the Processing Status instead of deleting the record from the Queue. Please advise.<br /><br />DECLARE @ReportExtractID int;<br />SELECT @ReportExtractID = (SELECT TOP 1 ReportExtractID <br /> FROM ReportExtractQueue WITH (rowlock, readpast)<br /> WHERE ProcessingStatus = 'Q' -- InQueue<br /> ORDER BY Priority); <br />IF @ReportExtractID IS NOT NULL <br />BEGIN<br /> UPDATE ReportExtractQueue WITH (rowlock)<br /> SET ProcessingStatus = 'P' --InProgress<br /> WHERE ReportExtractID = @ReportExtractID<br /> SELECT @ReportExtractID AS ReportExtractID<br />END;Anonymoushttps://www.blogger.com/profile/03957206235425359373noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-13862510265862188082014-06-30T11:32:53.029+02:002014-06-30T11:32:53.029+02:00Thanks Paul!Thanks Paul!Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-88227865174600976702014-06-30T10:49:14.596+02:002014-06-30T10:49:14.596+02:00Joost - Immense piece of work. Thanks for sharing....Joost - Immense piece of work. Thanks for sharing.Paul Khttps://www.blogger.com/profile/16379116665397993065noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-89442621747849107742014-06-10T10:02:01.647+02:002014-06-10T10:02:01.647+02:00This solution/framework is for independent package...This solution/framework is for independent packages only (for example staging). You can change the order of execution because it's a FIFO construction, but package 3 can still be busy while packages 4, 5 and 6 have already finished.<br />The only thing you could do is to have multiple versions of this package. Each executing its own set of packages.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-61397416151627662502014-06-10T01:19:16.136+02:002014-06-10T01:19:16.136+02:00Great Post. Quick question: How would handle multi...Great Post. Quick question: How would handle multilevel dependency with this framework?Petsyhttps://www.blogger.com/profile/08056490114317953370noreply@blogger.com