Stored procedure vs. DTS/SSIS for data inserts in SQL Server?

I would suggest that if your transforms are straightforward; i.e. , if they can be easily coded in a couple of SELECT statements, then a simple stored procedure with some SELECT INTO statements is your best option. Very efficient, and fewer moving parts to maintain.

Up vote 1 down vote favorite share g+ share fb share tw.

The source data lives on the same SQL server as destination table. Sql sql-server database database-design link|improve this question asked Jul 29 '11 at 18:11Tamila685 40% accept rate.

I would suggest that if your transforms are straightforward; i.e. , if they can be easily coded in a couple of SELECT statements, then a simple stored procedure with some SELECT INTO statements is your best option. Very efficient, and fewer moving parts to maintain.

On the other hand, if your transformation logic is very complex and/or changeable, consider SSIS. It exists to allow you to model and maintain complex, multi-step transformations. Good luck!

I think you have to ask yourself a bunch of questions before you can answer this one. Here is what I go through... One time or repeated? This is mostly to determine whether I need to answer the support and performance questions.

If it is a one time transfer, just do it whereever you feel most comfortable and archive the source code for WHEN you have to do it again. What are the other developers in my shop using/most comfortable with? Here, I do a lot of the SSIS stuff and they do mostly just SQL (and VB).

Since I want them to support it when I am away, I will probably build it into an SP if the task isn't too complex. Will this run at night when the server load is low or does performance tuning matter? For daytime stuff, I will almost always build the stored procedure, since that is easier for me, or someone else, to dig into and tweak for performance issues.

If performance is less of an issue, I am more likely to use SSIS. This is not because I think the INSERT is faster, just that it is easier to tune after the fact.

Bill, thank you for your reply! – Tamila Jul 29 '11 at 18:37 All of those tasks are repeated/scheduled tasks that will run daily or monthly. Some of them run during production hours, others - during off hours.

I am not very concerned about other developers since I am the only one who deals with this, and I am equally comfortable with both solutions. Performance is usually not a big issue since the data volumes are not that large. Right now I have a mixture of both solutions in my databases, and I'd like to have some consistency there, so I am looking for the best practices.. – Tamila Jul 29 '11 at 18:49 I'd say best practice is to pick one and stick with it, so the next person can find all the pieces.

Always remember, the next developer, who knows nothing about how it was done, might be you next year. – Bill Jul 29 '11 at 19:45.

I always use Bulk Insert. You will need to drop the indexes and at the end rebuild them. msdn.microsoft.com/en-us/library/ms18836... ALTER INDEX indexName ON Table DISABLE Bulk Insert ALTER INDEX indexName ON Table REBUILD.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.