July 18, 2008 at 8:05 pm
What are the difference between stored procedures and SSIS ?
July 20, 2008 at 2:50 am
Ketan Patel (7/18/2008)
What are the difference between stored procedures and SSIS ?
Stored Procedures :
A pre-complied collection of sql statements, processed as a unit. Example: sp_who, has queries that will show you the who has logged into the database at that time. We run the stored procedure at once like exec sp_who.
Stored procedures are very usefully as when we need to call a set of queries continuously through out the code. We could combine all the queries in a stored procedure and call the stored procedure, instead of calling all the queries.
SSIS:
SQL Server 2005 Integration Services, is a new feature in sql 2005, previously in sql 2000 we had DTS (data transformation services). With SSIS we can transfer data from one location to other, from one server to other. Refer :http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx
🙂
July 20, 2008 at 10:15 pm
One other key difference is that Stored Procedures are stored inside a database whereas SSIS is a service that runs on a SQL server - SSIS packages are run by the SSIS service but can be stored on the SQL server or on a file system folder somewhere else.
Kristen Hodges
b. http://bi-tch.blogspot.com
July 20, 2008 at 10:33 pm
kristen (7/20/2008)
One other key difference is that Stored Procedures are stored inside a database whereas SSIS is a service that runs on a SQL server - SSIS packages are run by the SSIS service but can be stored on the SQL server or on a file system folder somewhere else.
Correct me if wrong: I think SSIS packages are also stored in a database..MSDB??
July 21, 2008 at 1:04 am
In SQL 2000 DTS packages were always stored in MSDB. In SQL 2005, you have options. Yes you can technically store it in MSDB if you choose to but I thought that bringing that up might confuse the person asking the question as it may not assist them to understand the difference between SSIS and stored procedures. 🙂
Kristen Hodges
b. http://bi-tch.blogspot.com
July 21, 2008 at 1:20 am
true 😉
July 22, 2008 at 7:54 am
It is up to the user to deploy them in a database or a filegroup.
July 22, 2008 at 10:36 am
Actually, in SQL 2000 you could store DTS packages to files as well, but I digress.
With Stored Procedures, you're working with Transact SQL statements. It has all of the capabilities (and limitations) of that environment. The Stored Procedure runs under SQL Server, so access to resources is from a SQL Server perspective. With 2005 you can add assemblies, so you can make your stored procedures a much more powerful environment than ever before.
With SSIS, you're working with SSIS objects and possibly some Active X script. You'll want and need to break your process down into some fine grained chunks. (It takes some getting used to.) But then I believe you'll find it fast, scalable, and down right flexible. SSIS is more of a visual environment as opposed to a script. ( Well designed, people "get it".) SSIS helps you operationalize your processes, since everything about the package definition can be easily exposed to a job.
Now I know some DBA will see this note and take me to task for this view (yada, yada), but I find SSIS to be easier to use to manipulate files and data, in particular before importing in to SQL Server. In general, I find it's a better tool to use for batch applications, where I can kick off multiple processes at the same time. Stored procedures are better for defining discrete, "units of work" that are executed many times, and are often more fundamental building blocks in an application. In general (no, not always), the design breaks down thus: Jobs --> packages --> stored procedures --> SQL Statements.
Cheers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply