For years I've had problems with SSIS deployments to production. In fact, I completely abandoned SSIS packages because it was so difficult to deploy to production (in SQL Server 2005): I always ended up with errors that would require me to hand-edit the file and hard-code paths. That, despite the fact that my config files have the database connection strings and file paths in them. And despite the fact that my packages usually work fine on the test server but fail in production. After years of this problem, it suddenly occurred to me that one piece in particular is probably at fault. But given that I do not have access to production such that I can investigate, it will always be a hypothesis: using a template that sets various properties with the help of variable expressions.

Problem: SSIS 2005 (actually, Microsoft SQL Server Business Intelligence Studio) crashes every time you click on an OLE DB Source to edit it. (there are probably similar errors for OLE DB destinations).

Solution: oddly enough, synchronize a few DLLs:

Version of the assemblies ‘msmdlocal.dll’ & ’Msmgdsrv.dll’ must be the same of the ones installed into “%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\” and the ones installed into “%ProgramFiles%\Common Files\System\Ole DB” location.

If it is not same for any or both of these DLLs, then replace the ones in ‘PrivateAssemblies’ with the ones from ‘ole db’.

Hat tip: Calculation tab not working – SQL Server 2005 Analysis Services

I've been having problems migrating some SSIS packages from localhost (32bit) to the server (Windows Server 2003 64bit, SQL Server 2005 Enterprise SP2). These packages have Excel data connectors, and hence must be run in 32 bit mode. When I try to run them with the 32bit dtexec, I get the "generic" error "the package failed to load" (0xC0010014). I stripped down the package until I found the source of the error: a ForEach container, using the File Enumerator with the directory set to a variable. When I remove the variable setting it works, when I add the variable expression it fails. Note: this does not fail in the 64bit dtexec.

I read KB 913817 and determined that this was not causing my problem. When I open the package in Business Intelligence Studio on the server itself, I get an error when I try to edit the expressions on the ForEach loop (but only when the Enumerator is set to File).Poking around the SSIS DLLs, I found c:\program files (x86)\microsoft sql server\90\dts\foreachenumerators\ForEachFileEnumerator.dll. Re-registered it with regsvr32. Voila, problem solved.

Problem: I have an SSIS package, MyPackage, stored on MyServer in the Package Store. I create an Agent to run the package, running under a SQL account hooked up to a proper proxy and credentials for Windows authentication. Works in development, doesn't work in production: the agent gets the error Connect to SSIS Service on machine "MyServer" failed: Access is denied.

SQL Server 2008 will be coming out sometime this summer (in theory). At last week's TechFuse event in Minneapolis, and in blogs I sometimes read, I've started to pick up on a number of useful features and improvements that should make one strongly consider upgrading when the Release to Market (RTM) is ready (certainly this is not all-inclusive):

Problem: In SSIS, you want to update a variable from a Script Component embedded in a Data Transform task. You get an error message reading "the collection of variables locked for read and write access is not available outside of postexecute". What's up?

Problem: you have an SSIS package that you want to reuse, as a template, in a number of new packages.

Solution: follow Microsoft's simple instructions.

Problem: In SQL Server Integration Services (SSIS), you're trying to import from a data dump from another database system that has different data types from SQL Server. While the conversions look pretty straigh forward, you get failures of the type "The value could not be converted because of a potential loss of data.". But despite the mismatch data types, you can't see anything wrong.