如何以循环顺序多线程/并行运行SSIS [英] How to Multithread/parallel run SSIS in a Round Robin order
问题描述
我需要运行SSIS包来同时加载大约20个实体。但是，它需要确保包装不会彼此步进，例如，不要尝试通过多个进程执行相同的加载。
I need to run SSIS package to load about 20 entities at the same time. However, it needs to make sure that the packages do not step each other, e.g. not try to execute the same load by multiple processes.
环境是Azure SQL托管实例，我已将Integration运行时和已部署的SSIS包配置到Integration Serviced目录。
The environment is Azure SQL Managed instance and I have configured Integration run time and deployed SSIS packages to the Integration Serviced catalog.
我有一个驱动程序表，SSIS包在其中查询以加载数据。从驱动程序表中，包然后提取并加载每个相应工厂的数据。
I have a driver table where the SSIS package queries to load the data. From the driver table, the package then extracts and loads data for each corresponding plants.
这是表结构
所以，SSIS包需要同时装载所有植物。加载完成后，完成值将更改为1（完成）。将状态'N（未运行）加载到'R'（正在运行）和'L'（已加载）。
So, The SSIS package needs to load all of the plants at the same time. Once a load is done then the completion value changes to 1 (complete). Load status 'N (not run) to 'R' (Running) and' L' (loaded).
我正在尝试使其动态而不会使进程干扰（即不是两个）或更多的过程开始加载相同的工厂）。
I'm trying to make it dynamic without the processes interfering (i.e. not two or more process starts the load same plant).
M. Chowdhury
M. Chowdhury
推荐答案
Y.Chowdhury，
Hi Y.Chowdhury,
对不起，不清楚到底是什么你正试图这样做。
Sorry, not clear what exactly you are trying to do.
包控制流程中有什么？只有数据流任务？
What's in the package control flow? Only Data Flow Task?
以下是一些可能有用的提示。
Below are some tips that might help.
1， 我需要运行SSIS包来同时加载大约20个实体。
1, I need to run SSIS package to load about 20 entities at the same time.
默认情况下，在同一个容器中，所有的任务都不是由Precedence Constraint链接，将并行启动。
By default, in the same container, all the tasks, which are not linked by Precedence Constraint, will start in parallel.
但是，并行运行的任务数量受Package Property：MaxConcurrentExecutables的限制。在您的情况下，它应设置为20.
But, the number of tasks run in parallel, is limited by Package Property: MaxConcurrentExecutables. In your case, it should be set to 20.
SSIS中并行执行MaxConcurrentExecutables
2， 它需要确保包不会互相支持
2, it needs to make sure that the packages do not step each other
您可能需要为每个线程指定启动PlantNumber。并在每个线程的开头，检查工厂当前状态。
You may need to specify the start PlantNumber for each thread. And at the begin of each thread, check the Plant current Status.
3， 那么，SSIS包需要同时装载所有植物。加载完成后，完成值将更改为1（完成）。将状态'N（未运行）加载到'R'（正在运行）和'L'（已加载）。
3, So, The SSIS package needs to load all of the plants at the same time. Once a load is done then the completion value changes to 1 (complete). Load status 'N (not run) to 'R' (Running) and' L' (loaded).
我认为您可以使用OLE DB命令更新状态/已完成，在每个数据流任务的开始和结束时。
I think you can use OLE DB Command to update the Status/Completed, at the begin and end of each data flow task.
这篇关于如何以循环顺序多线程/并行运行SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
