What are the differences between File System and SQL Server deployments for SSIS packages?

Question

I have an SSIS package that needs to be deployed to SQL Server agent.

It has 2 external dependencies (2 assemblies, both installed in the GAC)

Now the package runs just fine under a File System Deployment

but when we deploy to SQL Server agent it fails with 'Object reference not set to an instance of an object' within the task that requires 1 of the external dependencies

Just to let you know, this external dependency is an Interop assembly to the right fax com api

What is the main differences between File System deployment and SQL Server deployment. I think if I can understand this a bit more then I may be able to diagnose the issue.

Answer 1

File system SSIS deployments (the only kind I've done) store SSIS packages as files on the OS. SQL Server deployments, if I have it right, first load the packages to be stored in a "deploymemt-only" file, which is later used to load them back into a different instance of SQL Server. (I don't know what database they're loaded into--in SQL 7.0 and 2000, DTS packages were stored in msdb, so I'd look there first. Maybe undocumented table sysdtspackages?)

Personally, it all looked so darn confusing and hard to maintain over time that I just stuck with files. Weaker security, and I'm not sure what other benefits I'm missing out on, but when updating I just copy over new files and I'm done.