Jump to content

SSiS Packages in MS SQL Server 2005


Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...