-1

We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps:

mainly our store procedure r to have compare the preset date to past date , and comparing id between the files and also some joins. update table r take place.

can u help me with clear steps to do...

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).

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

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

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

thank you in advance..

user3775057
  • 1
  • do you know what each line of the sp does? – KrazzyNefarious Jun 25 '14 at 12:07
  • I did not understand your requirement. You have a set of stored procedures which does some kind of comparison, through a set of queries right?. SSIS is used mainly to do an ETL process. In your case, you are planning to call the ssis packages through SP. Then may be schedule these packages. What difference does it really make?? Pardon me, If I read it wrong. – Jithin Shaji Jun 25 '14 at 12:11

1 Answers1

0

There is no easy way unless you write SSIS package and use execute SQL task to call your stored proc. There is no such wizard that would let you do that. Imagine you have all your program written in VB.net and how easy it to convert to C++? Not easy. Unless you rewrite it.

It seems, you are trying to replicate same functionality of SP into SSIS packages(SSIS version of SP), right? First your have to understand what each stored procedure does, what types of inputs are there and how it's execute, what is outputs. So basically you have to figure out - are there any CRUD operation going on.

If you don't have any documentation for these stored procedure, then you need to create one. Documentation will give you some sort of blue print for SSIS packages creation.

If you don't want to go for documentation path, then there are few online tools which can create flow charts from TSQL code. so that you can have something to start with and will get some logical idea what you have to do in your ETL process and then you can easily move your logic into the 'Execute SQL' task.

There are few SO threads which will provide some way to solve the problem.

Converting Stored Procedures to SSIS packages

SQL Server stored procedure conversion to SSIS Package

Community
  • 1
  • 1
Vikramsinh Shinde
  • 2,742
  • 2
  • 23
  • 29