Deploy to SQL Server or flat files

I'm in a quandry about how to deploy my SSIS packages and was hoping someone has some experience of the same.

There are 2 approaches: 1) Deploy to SQL Server. 2) Deploy to .dtsx files.

I suppose the default approach is to deploy to SQL Server however I have a problem with that. My packages contain Execute Package Tasks which use different connection managers depending on whether the package that they are calling is on SQL Server or in a .dtsx file.
That means that my packages have to contain 2 connection managers for each Execute Package Task - one for pointing to a package deployed to SQL Server and one for a package that is still in development and hence sitting in a .dtsx file. There is also the overhead of managing the deployment to make sure my Execute Package Tasks are pointing to the same place.

For this reason I am contemplating just leaving my packages as .dtsx files even when they are deployed to a production server.

Has anyone else encountered this problem? How have you dealt with it?
Has anyone found a distinct advantage of one approach over another?

Any comments welcome.

Thanks
Jamie

On a side note, what is the performance impact on FF deployment compared to DB?

|||Here were the responses I got from project REAL team. Wish they'd throw their stuff on the web for everyone to see how they did it.

---

From: Gary Cabana
Sent: Tuesday, September 06, 2005 04:13
To: Grant Dickinson
Subject:

Grant, for project REAL, you have one master workflow package that calls execute package tasks with file based connections, correct? How does this work for migrating the code from a development machine to the production box? In particular, when the package starts, are you using a configuration to set the connection string for the execute packages’ file based connection, or is it implemented such that the file locations of the packages being called cannot change?

thanks

---

From: Grant Dickinson
Sent: Tuesday, September 06, 2005 12:33 PM
To: Gary Cabana
Subject: RE:

Hi Gary

We originally had the file locations in the config as you have mentioned, but we realised that in our case we could use a convention of always having the files in the same place, eg c:\etl - thus the configs were redundant and I removed them. If you cannot use such a convention then the config worked fine and I'd recommend that - remember to secure the folder containing the packages if need be. I cannot comment on any experiences using the SQL store for the packages since we have not used that option in REAL.

Kind Regards

Grant Dickinson

---

From: Gary Cabana
Sent: Tuesday, July 26, 2005 13:44
To: Grant Dickinson
Subject: RE: Project REAL questions

One more question:

Did you use multiple packages or combine all the ETL into one giant package with multiple data flows? I’d like to use multiple packages so that development can be done in parallel.

If multiple packages, did you do the execute package in sql server or execute in the file system and why?

From: Grant Dickinson
Sent: Tuesday, July 26, 2005 5:09 PM
To: Gary Cabana
Subject: RE: Project REAL questions

Multiple - as with any code project I prefer modularity. As you say, this makes team development easier too. Currently I am using the file system, no particular reason except that it was easier since the VS project is file-based, as is SourceSafe. Let me know if you find any interesting reasons to use SQL instead.

---

From: Gary Cabana
Sent: Tuesday, July 26, 2005 5:13 PM
To: 'Grant Dickinson'
Subject: RE: Project REAL questions

The dev group said performance was the same.

One thing I don’t like about the file approach is that you need to create a new connection for each execute package you’re executing. Although, the current CTP 15 has the same problem for creating a new connection for each package that resides in the sql server (although Kirk said it’s a bug and I submitted it).

thanks

|||Gary,
That's superb stuff, thanks very much. If its good enough for Project REAL then its good enough for me!!

-Jamie|||The performance impact should be minimum.

The location of the package only affects the load time (i.e.
load from file vs. establish SQL connection and load from SQL table),
it does not matter during execution. For typical packages that run
several minutes to hours, the time to read package from file or
from database (less than a second, unless the server is really
overloaded) should not impact anything.

I think the decision should be driven by administative habits
and convinience. E.g. many people prefer SQL deployment
since they already backup SQL database regularly, and thus
get SSIS package backup for free. But many people backup
file system as well and prefer simplicity of file deployment.|||Package Access is another consideration when deciding the deployment option. Once Package is deployed on Sql server, User Access to the package is controlled at the database level. Whereas in the oher case its filesystem level.

For a scenario where many users are executing a package, having it on a centralized SQL server is just more simple. Esp. in case SSIS package connects to multiple databases. Its more secure rather than having connections set in a package saved on file system.|||

Suresh Bansal wrote:

Package Access is another consideration when deciding the deployment option. Once Package is deployed on Sql server, User Access to the package is controlled at the database level. Whereas in the oher case its filesystem level.

For a scenario where many users are executing a package, having it on a centralized SQL server is just more simple. Esp. in case SSIS package connects to multiple databases. Its more secure rather than having connections set in a package saved on file system.

All good reasons. However the issue regarding deployment that I mentioned earlier swings it.

Thanks all.

-Jamie