SQL Server存储过程转换为SSIS包 [英] SQL Server stored procedure conversion to SSIS Package

问题描述

问题：目前，我们有许多存储过程（最长可达10,000行），这些存储过程是由不同的开发人员在过去10年中针对各种要求编写的。现在，很难管理那些复杂/冗长的存储过程（没有适当的文档）。

Problem: currently we have numerous stored procedures (very long up to 10,000 lines) which were written by various developers for various requirements in last 10 years. It has become hard now to manage those complex/long stored procedures (with no proper documentation).

我们计划将这些存储过程移至SSIS ETL软件包中。

We plan to move those stored procedure into SSIS ETL package.

有人这样做过去了吗？如果是，则应该采取什么方法。

Has anybody done this is past? If yes, what approach should one take.

是否有人可以就将存储过程转换为SSIS ETL软件包的方法提供建议。

Appreciate if anybody could provide advise on approach to convert stored procedure into SSIS ETL Packages.

谢谢

推荐答案

我以前做过，对我的团队来说行之有效的是逐步重构，从原始源开始，然后迭代重构工作。

I've done this before, and what worked well for my team was to refactor incrementally, starting with the original source, and then iterate the refactoring effort.

第一步是尝试将存储过程逻辑模块化为我们链接在一起的Execute SQL任务。每个任务都经过测试和批准，然后我们将进行整合并确保新流程与遗留过程的结果相匹配。

The first step was to attempt to modularize the stored procedure logic into Execute SQL tasks that we chained together. Each task was tested and approved, then we'd integrate and ensure that the new process matched the results of the legacy procedures.

在这一点之后，我们可以将个人划分在整个团队中执行SQL任务，并对我们是否可以进一步将Execute SQL任务中的SQL重构为本地SSIS任务的分析进行负载平衡。

After this point, we could divide the individual Execute SQL tasks across the team, and load-balance the analysis of whether we could further refactor the SQL within the Execute SQL tasks to native SSIS tasks.

每个重构都是单独进行的经过单元测试，然后进行集成测试，以确保整个过程的输出仍然像旧程序一样。

Each refactoring was individually unit tested and then integration tested to ensure that the overall process output still behaved like the legacy procedures.

这篇关于SQL Server存储过程转换为SSIS包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！