How to run SSIS Project as a SQL Job

  • 2Tall

    SSChampion

    Points: 14590

    Comments posted to this topic are about the item How to run SSIS Project as a SQL Job

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • ken.trock

    SSCertifiable

    Points: 5147

    Nice, thanks for this article. When you deploy, at least the 1st time, you have to go find the .ispac file. You should be able to deploy straight from Visual Studio.

    Ken

  • danielk1

    SSC-Addicted

    Points: 462

    Very detailed. Will likely use this in the future. Makes sense to have to setup this information in the catalog and environment areas.

    Thank you

  • 2Tall

    SSChampion

    Points: 14590

    Thanks for the feedback.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • mis-508484

    SSC Journeyman

    Points: 87

    I use the "Save Copy of package" feature In Visual Studio project, choosing "SSIS Package Store" as package location, then when creating the SQL job browse for saved package. Is this wrong?

  • ryanbesko

    Ten Centuries

    Points: 1245

    I think the title of this article is misleading. It should be called "How to run SSIS Package as a SQL Job" instead of "How to run SSIS Project as a SQL Job". When I saw project I thought to myself, wow, I didn't know you could schedule an entire project to run.

  • ken.trock

    SSCertifiable

    Points: 5147

    I use the "Save Copy of package" feature In Visual Studio project, choosing "SSIS Package Store" as package location, then when creating the SQL job browse for saved package. Is this wrong?

    It sounds like you're still in the package deployment model. Phil's article was about deploying a number of packages together as a single SSIS project.

    Ken

  • chuck.forbes

    SSCarpal Tunnel

    Points: 4288

    Just curious, why would this method be preferable over just creating a SQL Agent job and directly executing the dtsx? I've seen references to uploading SSIS jobs into the database before, but I haven't seen a good discussion on it's benefits vs the alternative.

    Thanks,

    --=Chuck

  • tmagney

    SSC-Addicted

    Points: 479

    For what it's worth, setting up the Environment with variables for the FTP credentials isn't really necessary. You can configure the package without using the Environments, and simply enter in the username and password directly into the package configuration for the connection manager. It's a matter of preference, but I find doing it this way removes a layer and minimizes complexity.

  • ken.trock

    SSCertifiable

    Points: 5147

    chuck.forbes (1/6/2017)


    Just curious, why would this method be preferable over just creating a SQL Agent job and directly executing the dtsx? I've seen references to uploading SSIS jobs into the database before, but I haven't seen a good discussion on it's benefits vs the alternative.

    Thanks,

    --=Chuck

    If the package is part of an SSIS project you can take advantage of project parameters and connection managers; unlike when copying a single .dtsx.

    Also, the reporting of a job execution is more robust when the job is run out of ssisdb. It's an SSRS report. The reports have A LOT in there, which can be good or bad IMO 😉

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

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