Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Should you save to a file or to SQL Server?

SSIS enables you to save your packages either to SQL Server or to the file system.

Kirk Haselden has posted a useful blog explaining the pros and cons of each of these approaches. To that I would like to add the following:


Another advantage of saving to a file is that you are not dependant on having access to a SQL Server instance. This sounds like an obvious thing to say but consider...

With DTS 2000 you could save a package to a file system but you were still dependant on a SQL Server instance to execute the package. Now that the SSIS runtime can be installed seperately from SQL Server it is now possible to store and execute a Microsoft ETL routine without needing access to a SQL Server instance. That HAS to be an advantage because you have less components to manage (and pay for)!



UPDATE 6th May 2005

Allan has advised me that it is in fact possible to run DTS2000 without a SQL Server instance. You simply need to redistribute the necassary DLLs.

Thanks Allan!



Published Thursday, May 05, 2005 3:42 PM by jamie.thomson



jamie.thomson said:

With 2005 it may be that you HAVE to have an instance of SQL Server as well. This is borne out in the docs for the fuzzy grouping transform

"The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and choosing a canonical row of data to use in standardizing the data. The transformation requires a connection to an instance of SQL Server 2005 to create the temporary SQL Server tables that the transformation algorithm requires to do its work. The connection must resolve to a user who has permission to create tables in the database."
May 7, 2005 1:56 PM

jamie.thomson said:

Also you mention this may be less items for which you must pay. Have a look at the discussion between Kirk and myself re what you have to buy. Not good !
May 7, 2005 1:58 PM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems