Below is a step by step guide on how to use SSMS to run and schedule SSIS packages.
Setting up integration catalog
We need to set up our Integration Services Catalog in SSMS to be able to hold our deployed SSIS packages. Open SSMS as Administrator Right click Integration Services Catalog and select create Catalog.
⦁ Check Enable CLR Integration. Keep the Catalog name as SSISDB. Enter and retype a password.
⦁ Click OK. And see the newly created folder in SSMS.
Deploying the package
Open Visual Studio 2017 and navigate to the SSIS project that contains the package you want to use. Right click on the project and select Deploy.
⦁ Click Next
⦁ Select SSIS in SQL Server
⦁ Type in the name of your SSMS server. Select Windows Authentication, then click connect. After connecting you can click browse to select the path. This path is based in your SSISDB catalog made above. You may want to create a new folder.
⦁ Review then click Deploy
⦁ Verify the project deployed successfully then close.
⦁ If SQL server agent is not running, we need to start it. In SSMS right click SQL Server Agent and click start.
⦁ Verify it is running with the green play icon.
Scheduling the job.
Drill down into the SQL Server Agent and right click on the Jobs folder, select New Job.
⦁ Name the job in the General Tab, then move to the Steps tab.
⦁ Click New to create a new step. Name the step. Make the Type: SQL Server Integration Services Package. Run as: SQL Server Agent Service Account.
⦁ Under the Package tab: Package source: SSIS Catalog, Server: Name of SQL server, Use Windows Authentication, Package: Browse to SSIS package you wish to use.
⦁ Click Ok and go to the Schedules tab. Click New to create a new schedule.
⦁ Here you can set up the recurrence you want. The above example happens every day at 5:30 pm.
Job History
You can also run these jobs on a case by case basis by right clicking the created job and selecting Start Job at Step. After the job has ran you can select View History to see the results of the run.
⦁ In this example my SSIS package was pulling data from the D365 DMF and exporting the file to a local folder. I can see it successfully ran, and I can view my exported files!
For more SSIS development blogs, read on!
For more tech blogs, subscribe to the code_marks news letter: http://eepurl.com/gZCMQz
3 Comments
Pingback: How to access SFTP with SSIS using SSH.NET | Marked Code
It’s actually a great and helpful piece of information. I am satisfied that
you simply shared this useful info with us. Please stay us
up to date like this. Thanks for sharing.
Ηi there, just wanted to mention, I loved this post. It was inspiring.
Keep on posting!