Scheduled SSIS Package Does Not Run in SQL Server 2005

I thought I’d share a little information with you today about something I came across when upgrading to SQL Server 2005, so you can avoid some of the frustrations I had to deal with.

In our SQL Server 2000 environment we had a few DTS packages which import data every night from text files via the scheduler. These packages were a pain in the butt to set up, because they were importing data from text files that weren’t delimited. This meant defining all those columns as fixed width using that horrible interface. Anyhow, when upgrading to SQL Server 2005 the last thing I wanted to do was re-create these DTS packages.

Luckily SQL Server 2005 has a conversion utility to convert these DTS packages to SSIS packages. This worked well and I had my packages in SQL Server 2005. I created a job to run my SSIS packages and that worked fine too. The problems arose when I tried to schedule these jobs. No matter what I did, the scheduled job always failed (even though I could right click on the job name, click Start Job at Step.. and the job would run successfully).

Each time I would get an error that said, “Failed to decrypt protected XML node “PackagePassword” with error 0×8009000B”. I had no real password protections on this package, so I really wasn’t sure what this error meant. Oh, I forgot to mention one thing: when we upgraded to SQL Server 2005 we also started on a new server, one that was 64-bit. It turns out this is an important detail.

Anyhow, that was the problem. The solution is a simple one, once you know it. When setting up your job, rather than defining the Type as “SQL Server Integration Services Package” as below,

instead choose “Operating system (CmdExec)”.

In the Command Window enter the following string:
“C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe” /SQL “namehere” /SERVER ServerNameHere /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E

Make sure to replace namehere with the name of your SSIS Package and ServerNameHere with the name of your server. Schedule this and it should run, using the old DTS execution engine.