Scheduling an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

Scheduling a DTS package

I am using Enterprise manager with an MSDE engine. I have created a DTS package that updates a table in one of the databases. If I right click and choose "Execute Package" it runs...No sweat. When I try to use the scheduler to run the job every hour, it always fails between 1 and 10 seconds into the job...It only returns the error "Failed During Step 1."

I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?Under Management>Jobs, right click on the job in question, and select "Job History". There should be a checkbox that says "Show step details". Clicking that will expand the information. Check to see if there's more information in there.|||Probably a security issue. Wen you run the package interactively it uses the logged in user, when running from the job queue it uses the configured user account of the DTS Package.

Open your package, click Package->Properties->Logging|||Thanks for tips, the error is:

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the path specified.

Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

I checked the path and it's correct, and it finds the file when I run it manually.|||Check the server to see if the file exists in the exact same filepath as on your machine. I believe that when you run a DTS package interactively, it resolves file names locally, but when executed from the server via SQLAgent it will resolve the filepaths from the server.|||i concurr
i suck at typing in filepaths so i always set the windows explorer to display the full path in the title bar and i just copy it from there.