Saving SSIS package in MS SQL Database

Gerhardpet
Gerhardpet used Ask the Experts™
on
When I save a SSIS package in MS SQL Database before importing data from a Pervasive database where does it save the SSIS Package?

I select "Save SSIS Package > SQL Server (not File System)

Next I need to know how to create a schedule for the package to run on a time interval.

Can someone help me?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Depending on the SQL Server version it saves SSIS packages in tables in the system MSDB database. You can query these but it's not generally necessary to do so.

To use the package you need to set up a SQL Agent job which will run the package as a job step. The SQL Agent job can be scheduled, you can also run SQL commands, etc from the job.

Hope that points you in the right direction.

John

Author

Commented:
That makes sense but I can't find how to schedule a job base on the SSIS package I save.

I'm on SQL 2008 R2.

Can someone point me in the right direction?
Commented:
These steps should help:

- open SQL Server Management Studio
- connect to your SQL Server
- Open SQL Server Agent
- Right click on "Jobs"
- Select "New job"
- Give the job a descriptive name
- Click on "Steps"
- Press "New" button
- Give the step a name
- Select "SQL Server Integrated Services Package" as the Type
- Package source will be SQL Server
- Enter your Server name in "Server"
- Find your saved package by pressing the "..." button

That should create the new job to run your SSIS package. You can schedule it under the "Schedule" section of the Job (see screen shot attached)

I hope that helps
newjob.JPG

Author

Commented:
Yes that helps. Can you also tell me where I can manually execute the package after I save it as "Save SSIS Package > SQL Server (not File System)"?

Thank you for the detail explanation. I'm pretty new at MS SQL and SSIS.

Author

Commented:
Great thank you very much for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial