0

There is A SUGGESTION to redesign all the SSIS package in a project.The thing is ,at the moment all functionality has been implemented using the T-SQL stored procedures(Execute SQL Task).I have been asked to redesign all those T-sql tasks to ssis components.

When I checked the stored procedures,these are not complex t-sql and it has to handles only small data sets.

I can not see any benefit of implementing all these using the SSIS Components.Please share your thoughts and experience / any other sugestions ,if you are an SSIS developer

user1254579
  • 3,901
  • 21
  • 65
  • 104
  • You say "I am going to redesign all the SSIS package in a project" and then say "I can not see any benefit of implementing all these using the SSIS Components" - so why are you redesigning it in the first place!? – Tony Mar 04 '14 at 12:17
  • 1
    Having been asked to maybe? – makciook Mar 04 '14 at 12:18
  • Hi,Actualy I have been asked to redesign it .I am reviewing the codes,before redesigning – user1254579 Mar 04 '14 at 12:33
  • 3
    I would argue against making such a change. SQL Tasks are much more portable. Remember when we all worked using DTS and then they switched to SSIS? Everything that wasn't a T-SQL task had to be re-written. Unless there's a *clear* benefit of changing things I would leave things as they are – gvee Mar 04 '14 at 12:37
  • 1
    If you're working internally all on the same server or in the same database, there's no advantage to using SSIS over the stored procedures, especially if they already exist and work. If you're trying to pull data in from external sources with some transformation, then SSIS is more likely to be of value. – user3358344 Mar 04 '14 at 12:38
  • 1
    I'd imagine the only benefit would be error handling, you would be able to track down issues if you were to split out the SP into DFT. However if everything is working correctly that could be a lot of work for little benefit. – Peter_R Mar 04 '14 at 12:40

1 Answers1

1

Standardization.

SSIS in general has a hugh disadvantage towards tsql - it pulls the data out (unless you just send SQL to the database. It's main advantage is a lot more flexibility and actual power than SQL (plus it can run on another machine, adding scalability).

In your case it seems somoene has decided to standardize on SSIS packages, likely for standarizing - and this makes a certain amount of sense. Not saying in this case, just that standardization is a good thing in general. The rest is opinion based - so I will not talk about it here.

TomTom
  • 61,059
  • 10
  • 88
  • 148