Database Reference
In-Depth Information
12. We can choose either to save a package in SQL Server (package) or File System (as a
file) with a protection level of 'Encrypt data' with a password/key, or rely on windows
operating system roles for access control.
The package store includes
msdb
database and folders in the
filesystem, for the better administration of the ETL process using
a backup strategy it is essential to save a copy of the package to
SQL Server, which is saved to the
sysssispackages
table.
For this recipe, we chose to create a simple ETL process to export Department table data from
AdventureWorks2008R2
database to a Flat-file destination (text file) with a protection level
of 'encrypt all data with password' option. The package has been saved to SQL Server with
the name Export Department Data (as shown in the next screenshot) and not chosen to
'Run immediately'.
1.
Now, we are at the stage where we need to schedule the saved package using SQL
Server Management Studio. Under the
Management
folder expand SQL Server Agent,
the option which will present the
Jobs
folder.
2.
Right-click on
Jobs
and choose New job to create a job.
3.
Name the job and choose the Steps options to create a new job step.
4.
Now choose the step type as SQL Server Integration Services Package.
5.
Choose the package source, which is SQL Server in our case.
Search WWH ::
Custom Search