SSiS Packages in MS SQL Server 2005

[Thirster42]

At my job I have an SSiS package (or .dtsx or whetever, it's late and i can't remember everything, I'll update the post with more information when I get to work) that pulls data from an Excel Spreadsheet (we're currently in the process of slowly weening the company off of spreadsheets and into centralized databases). I can execute the package just fine using a batch file, but I want to be able to set up a job using SQL Server Agent so that anyone can run the job from the SQL database, or have it automated to pull it in once a day.

Anyway, I look up the solution online, and it's not helping. I created the proxy agent and everything and assigned the proxy agent to the job, but it still won't execute the package.

If any of this makes sense, I could really use some help. Like I said, when I get to work I'll update the post with more information, including what the error is and what all I have done.

[PileOfMush]

If you enable xp_cmdshell you can use it to call dtexec on the server.

You can have a command like:

EXEC xp_cmdshell 'dtexec /dts "\File System\Folder\PackageName" /server ServerName;'

or if you want to fire off that command from batch file on a client machine that has client tools installed:

sqlcmd -S ServerName -E -d DatabaseName -Q "EXEC xp_cmdshell 'dtexec /dts ""\File System\Folder\PackageName"" /server ServerName;'"

Read the docs for dtexec and sqlcmd before using these.

Hope this helps or gets you pointed in the right direction. Good luck.