SSIS 最佳实践 - 做 2 件事情中的 1 件 [英] SSIS Best Practice - Do 1 of 2 dozen things

问题描述

我有一个正在处理队列的 SSIS 包.我目前有一个单独的包，它被分成 3 个容器1. 收集一些元数据2.做作业3. 重新检查元数据，根据我们认为发生的情况更新队列(成功失败的味道)

I have a SSIS package that is processing a queue. I currently have a singel package that is broken into 3 containers 1. gather some meta data 2. do the work 3. re-examine meta data, update the queue w/ what we think happened (success of flavor of failure )

我对速度不太满意，部分原因是我在仓鼠供电的服务器上运行，但这是我无法控制的.

I am not super happy with the speed, part of it is that I am running on a hamster powered server, but that is out of my control.

中间部分可能会提供改进的机会......有 20 个表可能需要更新.每个队列项将更新 1 个表.我目前有一个包含 20 个序列容器的序列.

The middle piece may offer an opportunity for an improvement... There are 20 tables that may need to be updated. Each queue item will update 1 table. I currently have a sequence that contains 20 sequence containers.

它们本质上都做同样的事情，但我想不出一种方法来抽象它们.

They all do essentially the same thing, but I couldnt figure out a way to abstract them.

每个框中的第一个框是一个空的脚本操作.如果表名匹配，则有条件流向胆量".

The first box in each is an empty script action. There is a conditional flow to 'the guts' if there is a match on tablename.

所以我打开了 20 个序列任务、20 个空脚本任务并进行了 20 个 T/F 检查.

So I open up 20 sequence tasks, 20 empty script tasks and do 20 T/F checks.

看黄/绿灯显示，这个好像很慢.

Watching the yellow/green light show, this seems to be slow.

有没有更有效的方法?我认为让它变得更好的唯一方法是将 20 个空脚本放在序列容器之外.这将节省的是打开容器.我不敢相信打开一个序列容器就这么贵.它是否可能每次都重新验证容器中的每个任务?

Is there a more efficient way? The only way I can think to make it better is to have the 20 empty scripts outside the sequence containers. What that would save is opening the container. I cant believe that is all that expensive to open a sequence container. Does it possibly reverify every task in the container every time?

只是钓鱼，如果有人有任何想法，我会很高兴听到他们.

Just fishing, if anyone has any thoughts I would be very happy to hear them.

谢谢

格雷格

推荐答案

您现在的主要问题是您正在 BIDS 中运行它.这旨在使包的开发和调试变得容易，所以是的，它会在运行时验证所有对象.另外，黄/绿灯显示"是向您展示包运行时发生的情况的开销更大.当您使用 DTSExec 或作为 Sql 服务器的计划任务的一部分运行它时，您将获得更好的性能.你在记录你的包裹吗?如果是这样，请从服务器运行并查看日志以验证该过程在服务器上实际花费的时间.如果到那时还是太久，那么你可以实现一些@registered user 的想法.

Your main issue right now is that you are running this in BIDS. This is designed to make development and debugging of packages easy, so yes to your point it validates all of the objects as it runs. Plus, the "yellow/green light show" is more overhead to show you what is happening in the package as it runs. You will get much better performance when you run it with DTSExec or as part of a scheduled task from Sql server. Are you logging your packages? If so, run from the server and look at the logs to verify how long the process actually takes on the server. If it is still taking too long at that point, then you can implement some of @registered user 's ideas.

这篇关于SSIS 最佳实践 - 做 2 件事情中的 1 件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！