Scheduling an SSIS package using SQL Server Agent

Having got past the other issues I’d had with creating my SSIS packages, I now needed to set them as scheduled jobs on the database. This again was not as easy as I thought it would be as there was a problem with permissions. I’d followed this codeproject page to help with permissions issues but my proxy still did not have enough permissions on the server to run the jobs so I went back to using SQL Server Agent.

I then came across a problem with the password the SSIS packages were using – I’d stored it within the package and this ran fine when run directly as an SSIS package but not when run by SQL Server Agent as a job. A bit of digging around online led me to creating a configuration file for the SSIS packages to use which would read in the password. This was done as follows:

• Edit a package in Visual Studio, and choose the ‘Package Configurations…’ option from the SSIS menu.
• If not already selected, tick the ‘Enable package configurations box’
• Click ‘Add…’ to open the configuration wizard
• Specify the configuration type as ‘XML configuration file’ and enter its name and location
• On the ‘Select Properties to Export’ page open up the properties for the database connection under ‘Connection Managers’ and put in tick in ‘Password’.
• Name the configuration and click finish

At this point I thought there would be a way to set the edit the configuration file to set the password within Visual Studio but there didn’t seem to be a link related to the package or the project, so I opened up the file in notepad and set it manually

• Double click the Database Connection manager you want to use the password for, and make sure the ‘Save my password’ option is unchecked.
• Now in Management Studio when you run the package manually, or when scheduling it as a job you need to add the configuration file you have just created under ‘Configurations’.

At least thats what I had to do to get it working for me.

Advertisement