SQL Job to execute SSIS Package

  • A Little Help Please

    SSCarpal Tunnel

    Points: 4764

    SSIS Package

    - Copys 4 files to local (All 4 files have dynamic file names which are todays date)

    - Emails the files (SendMail has been designed to look for these dynamic files)

    SQL Job

    Now I have created a job to run the SSIS Package...

    This fails on the 'Send Mail' Tasks

    Error

    "trial_bal_by_currency_11_SOD_1_LON_20090126.pdf"

    does not exist or you do not have permissions to access the file.

    End Error Error: 2009-01-26 11:33:50.09 Code: 0xC0024107

    Source: Send Mail Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned

    DTSER_FAILURE (1). Started: 11:33:49 Finished: 11:33:50 Elapsed: 0.891

    seconds. The package execution failed. The step failed.

    Problem

    1. If I check in the local folder the files are there!

    2. If I run the SSIS package manually within SSBID it works fine....

  • Greg Charles

    SSC-Forever

    Points: 45403

    It may be that, if you're using Windows authentication in the SMTP Connection Manager in the package, the SQL Agent account doesn't have access to the pdf files. Check the file security.

    Greg

  • Sahin Kaya

    SSC-Addicted

    Points: 461

    Hi,

    I faced a similar problem and could not solve the authentication issue, but found an alternative way like creating a batch file running the SSIS package and schedule that batch file through windows task.

    In order to do that, first create a text file with something similar to the following commands in:

    "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec" /f "path of package\package.dtsx"

    then save it with ".bat" extention.

    Then from the run of Windows start write Tasks, and schedule a task here to run the batch file on the intervals you want.

    Hope that helps as an alternative solution.

    Regards,

    Sahin.

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    can u please help me this in detail

  • Amar009

    Mr or Mrs. 500

    Points: 552

    The error you have posted makes me believe that you are just giving the <filename> to look for. Give the full path of the file like "C:\MySourceFolder\MyFileName". That should help.

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    after creating a .bat file .... how to schedule a task through windows scheduler

  • Amar009

    Mr or Mrs. 500

    Points: 552

    You need to go to Task Scheduler in Accessories > System Tools.

    The interface is a bit different in XP and Win 7 but do the same job.

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    thanks a lot.... i got it

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply