How to run SSIS Project as a SQL Job

  • 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

  • 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

  • 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

  • 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

  • Thanks for the article.

  • 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?

  • 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.

  • 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

  • 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

  • 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.

  • 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 10 (of 10 total)

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