The Project
I have a SQL Server 2005 SSIS Package that downloads a file from an ftp host, loads the file into a temp table on the local machine database, massages the data a little and then uploads to a web host database. Sounds fairly simple right? And it is, until you get to the part where you setup a job in SQL Server Agent to execute your package.
Problem 1 : ProtectionLevel
At first, the package would not even run. Immediately, I received some error about not being able to decrypt something. A quick google search and I quickly discovered that I needed to do something about the package's ProtectionLevel. If you don't know what that is, start here.
I opted for the EncryptSensitiveWithPassword protection level and choosing to customize the command line for the package running in SQL Server Agent because I wanted to just run it from the file system. Here are the steps you need to follow to get this working:
- In Visual Studio, select the package you want to modify the ProtectionLevel for
- Click in the designer for the package (don't click on any items on the designer)
- Now view the properties and look for the ProtectionLevel item and change it to EncryptSensitiveWithPassword
- Now set the PackagePassword property and save/build your project
- In SQL Server Agent, add/edit your job and select the step that will run your package
- Click the Command line tab (you'll be prompted to enter the package password)
- Select the Edit the command line manually option and add the following
- /DECRYPT <password>
Problem 2 : Windows Firewall
Once I got the package to run, I ran into another problem. It sat there for a long while, seemingly doing nothing (I was watching the target directory for the ftp downloaded file to appear) before finally failing with an FTP timeout error. I initially considered that it must be a Windows Firewall issue, but I didn't want to just turn off Windows Firewall or open up FTP for everything. I really wanted to just allow the process running my package to have access. The question was what process is that? sqlserver.exe? sqlagent90.exe?
The answer is neither. After digging around in the Event Logs I finally located the Windows Firewall logs and found the entry that gave me the answer. The executable that actually runs an SSIS package is DTExec.exe (located here: %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\DTExec.exe)
So, all you have to do is add a rule in Windows Firewall to allow DTexec.exe on any protocol, port 21 and bam!... everything works like a champ.
Hope this helps someone save some time and frustration.
