Cannot run SSIS package using SQL server agent
Hi all, I had import SSIS package to SSIS server and can run completely if I execute via SSIS. I have to schedule the package to run at night so I created SQL Server agent job for scheduling. But I found that the SQL Server agent cannot execute the SSIS package with the following error. ---------------------------- Started: 2:00:01 AM Could not load package "\File System\UMI_TOPS\MainSequence\SEQ_WEEKLY" because of error 0x80070002. Description: Unable to find the specified file. Source: MsDtsSrvr Started: 2:00:01 AM Finished: 2:00:01 AM Elapsed: 0.11 seconds. The package could not be loaded. The step failed.,00:00:01,0,0,,,,0 ---------------------------- So could you please kindly suggest me about this issue, Thank you in advance :)
July 16th, 2012 12:20am

Hi The error is because you dont have such file in Integration Server's File system, Just edit the job properties and specify the package location correctly. To Check Intergration Server? Connect the Integration Services from SSMS . Check the package whether its there on file system folder.
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 12:37am

>>>>have to schedule the package to run at night so I created SQL Server agent job for scheduling. Can you show us how you define it?Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing
July 16th, 2012 12:54am

The SSIS package location correctly. Please see in attached file. If you have a comment please help. photo
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 1:37am

I cannot see the attached file.Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing
July 16th, 2012 1:38am

Please see in the link below. picture
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 1:39am

Yes, but I wanted to see how you defined the step job that call the package and not how you schedule it...How you deploy the package? Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing
July 16th, 2012 1:44am

Sorry, I deployed the package using file system and when I created a job I point the job to execute the stored package. Please see in the link below. Thank you. http://upic.me/show/37404530 http://upic.me/show/37405211
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 2:10am

Below is command line from SQL Server Agent job, not sure that do I have to edit the following. /DTS "\File System\Package_name" /SERVER UMIETL01 /CHECKPOINTING OFF /REPORTING E
July 16th, 2012 3:08am

Try executing the same package from command line once and let us know.
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 3:10am

The result of using command in previous comment was error. Please see in link below. http://upic.me/show/37407222 So I create a job to call SSIS package in file system and found that it run successful(in SQL server agent).Please see the command below. /FILE "D:\SSIS\U-Marketing\Packages\UMI_TOPS\CF_CALDT_WEEKLY.dtsx" /CHECKPOINTING OFF /REPORTING E Now I wondering why the error log tell that "/DTS not recognized" when using job to run SSIS package in SSIS stored package. Please suggest. Thank you in advance.
July 16th, 2012 4:22am

The Command which you have tried is wrong, try like this, and let us know DTEXEC /DTS "\File System\UMI_TOPS\MainSequence\SEQ_WEEKLY" /SERVER UMIETL01 /CHECKPOINTING OFF /REPORTING V and one morething /FILE "D:\SSIS\U-Marketing\Packages\UMI_TOPS\CF_CALDT_WEEKLY.dtsx" /CHECKPOINTING OFF /REPORTING E which you have mentioned refers a file system path on your local disk drive.
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 4:57am

Thank a lot for your suggestion Kingxxx1. The command work fine on command line but after I used this command in SQL server agent job there's still error but difference from the old one, please see error log below. "Option "DTEXEC" is not valid. The command line parameters are invalid." So is it mean cannot using DTEXEC in SQL server ageni job? Please suggest. Thank you.
July 16th, 2012 5:23am

DTEXEC /DTS "\File System\UMI_TOPS\MainSequence\SEQ_WEEKLY" /SERVER UMIETL01 /CHECKPOINTING OFF /REPORTING V This command is to execute package from command promt, not from sql job. If this is working fine means some thing problem with your job. Try creating a new job, with the below command and let us know. /DTS "\File System\UMI_TOPS\MainSequence\SEQ_WEEKLY" /SERVER UMIETL01 /CHECKPOINTING OFF /REPORTING V
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 5:34am

Job fail with following log. Please suggest. "Could not load package "\File System\UMI_TOPS\MainSequence\SEQ_WEEKLY" because of error 0x80070002. Description: Unable to find the specified file." Thank you
July 16th, 2012 5:49am

Try deploy the package to SQL Server instead.Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 5:52am

Thank a lot Uri Dimant, it's working. Could you please tell me what is the difference between deploy the package on file system and SQL Server and why job failed when execute file system SSIS package. Thank a lot.
July 16th, 2012 6:09am

Hi CraYon, Will please share the command which you executed from SQL Job now.
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 6:24am

What is the difference between file system deployment and SQL server Deployemnt Mr.Uri Dimant?
July 16th, 2012 6:26am

Please see below, By the way this command was auto generate after job config. /SQL "\UMI_TOPS\CF_CALDT_WEEKLY" /SERVER UMIETL01 /CHECKPOINTING OFF /REPORTING E
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 6:29am

Hi C r a Y o n, File system deployment: Deploy packages and dependencies in a specified folder in the file system. SQL Server deployment: Deploy packages and dependencies in an instance of SQL Server. As for why job failed when execute SSIS package stored in file system, you should give the full path and name of the package, such as: C:\fordername\foldername\Packagename.dtsx. Please refer to the link about scheduling a SSIS package that is stored in a File System in SQL Server Agent Job: http://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/ Thanks, Eileen
July 20th, 2012 2:52am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics