Execute SSIS package using SQL Server Agent(Using Jobs):

Select imported package

Right click and select run package

In execute package utility editor click execute.

5. Execute SSIS package using SQL Server Agent(Using Jobs):

Open SQL Server Management Studio, Connect to Database Engine

& Select SQL Server Agent [Note: Ensure that SQL server Agent Service is start mode].

Select Jobs, Right Click and select new job Provide Job name as “Load for each loop container”

Select Steps page & Click new Step Name-Load for each loop container Type-SQL Server Integration Service Package

In General Tab, Package Source-File System Package-Click Browse

Select any package from list of package Click open

Click ok

Select schedule package & Click new

Provide the Job name as “Load for each loop container” & Schedule Type-Recurring Set the Frequency (When to start the execution of the specified package

Click Ok twice DBA Tasks in SS IS 2005

Data base Administrators can create database maintenance plans either by using Maintenance plan Wizard or by using SSIS Designer. By using maintenance plan wizard a very basic Maintenance plan can be created for all the system and user Data bases. However to create an enhanced work flow it is advised to create Maintenance plan using SSIS designer.

Page 60 Backup Database Task:

Different types of Databases backups to be performed; Such as Differential or Transactional log Backups based on the Recovery Model of the system or user Databases. To learn more about the different Recovery Models in SQL Server refers to the article titled

“Database Recovery Models in SQL Server”.

Check Database Integrity Tasks:

It can used to check the allocation and structural Integrity of all the user and system tasks within a database. This task also has an option to check the allocation and structural integrity of all the Indexes available within a database. This task internally Executes DBCC CHECK DB Statements.

Execute SQL Server Agent Job Tasks:

Execute SQL Server Agent Job Tasks can be used to run SQL Server Agent Jobs that are created on the SQL Server Instance.

Execute T_SQL Statement Task:

Execute T_SQL Statement Task can be used to execute to transact SQL Query against Databases. This task is only available when creating maintenance plans using SSIS Designer.

Transfer Database Task: from the network location to the destination server and then finally both databases are attached on the source and destination servers. This mode is transfer, but a disadvantage with mode is that the source database will not available during copy and move operation also, the person executing the package with this mode must be system admin and both source and destination instances.

- Online: In this mode, the task uses SMO to transfer the database objects to the destination server. In this mode, the database is online during the copy and move operations, but it will take longer as it has to copy each object from the database

Page 61 individually. Someone executing the package with this mode must be either system admin or Database owner of the specified databases.

Transfer Database Task:

Drag the Transfer Database Task “from the toolbox(which is normally on the left side) to the control flow pane. Right Click on the task and select Edit… as shown below:

In the “Transfer Database Task Editor”, select databases on the left and now you are ready to configure this task. Source connection is the property to specify the connection for the source SQL Server Instance, if you have already created a connection manager then you can reuse it here or can create a new one as shown below:

This will also need to be done to configure the destination connection property as well. Next you need to specify the values.

 Connections:

Source connection - The source Instance.

Destination Connection -The destination instance.

 Destination Database:

Destination Database Name- Name of the new database

Destination Database Files- Name and location of the database files.

Destination Overwrite-If the database already exists on the destination server it will give you an error, so if you want to overwrite the destination database you can set this property to true.

 Source Database:

Action-Whether you want to copy or move a database.

Method- whether you want the copy and move operation to be offline (in this case you also need to provide the network share name which will be used to transmit the database files)

Source Database Name-Name of the source database.

Source Database Files-Name and location of the database files.

Reattach source database – Is another property which you can set to true to reattach the source database after the copy operation.

Page 62 Once you are done with all the configurations you can hit F5 or Click on the play icon to execute the package. your task will turn yellow during the execution and then either red or green depending on the execution outcome .you can go to progress tab to see error messages if the execution fails. Although failure is rare, it is possible if your source database is smaller than the size of the model database on the destination server.

Transfer SQL Server Object Task:

The Transfer SQL Server Object Task is used to transfer one or more SQL Server Objects to a different database either on the same or another SQL Server Instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedure, user defined functions etc .not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Create another package in the same project and drag a ”Transfer SQL Server objects tasks” to the control flow. Right click on the task and then select edit. In the “Transfer SQL Server Object Task Editor” click on objects to set the different properties for this tasks as shown below:

Similar to the way you configure the source connection and destination connection for the Transfer Database Task, you need to configure these properties for this task as well. Source database is the name of the database from where your are copying the objects and destination database is the name of the database to which you are copying the objects to.

 Connection:

Source connection-the source instance

Source Database- Name of the source data Base Destination Connection- The Destination instance Destination Data Base-name of the new Data base

 Destination

Drop objects First - Drop selected objects on the target before copy

Include extended properties – While copying operations also include extended properties of SQL objects being copied

Copy data – While copying tables,transfer the data of the selected tables as well.

Existing Data – Whether to append r replace data

Copy Schema – Copy the schema of the objects being copied.

Page 63 Use Collation – Make sure collation of columns are approximately set on copied tables Include Dependent Objects – Include all the objects in copy operation which are dependent on selected objects.

- Destination Copy Objects:

Copy all objects – Do you want to copy all objects from the source database, if set to false, next property objects to copy will get enabled.

Objects to Copy – With this property you select types of the objects you want to copy. You can select all objects of one or more types or select particular version, type of objects selection will vary.

- Security:

Copy Database Users – Whether to include users Copy Database Roles – Whether to include roles Copy SQL Server Logins – Whether to include logins

Copy Object Level Permissions – Whether to include object level permissions - Table options:

Copy Indexes – Whether to include indexes Copy Triggers – Whether to include triggers

Copy Full Text Indexes – Whether to include full text indexes

Copy All DRI Objects – Whether to include referential integrity objects Copy primary Keys – Whether to include primary keys

Copy Foreign Keys – Whether to include foreign keys

Generate Script In Unique – Whether to create script in Unicode or not Transfer Jobs Task:

Transfer Jobs Task is used to transfer SQL Server agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from source server to the destination server.

Open BIDS. Drag a Transfer jobs Task from the Tool box to the control flow pane as shown below. Right click on this task and click on Edit.

Page 64

 Steps to Configure Transfer Jobs Task:

- Connections:

Source connections-Specify the connection for the source SQL Server instance, if you have already created a connection then you can reuse it here or can create a new one .

Destination Connection – Specify the connection for the destination SQL Server instance - Jobs:

Transfer All Jobs: If this is set to true it will transfer all jobs . If this is set to false you can select specific jobs you want to transfer.

Jobs List: This will be enabled if Transfer All Jobs is set to false. Then you can select specific Jobs to transfer. See the image below where I am only selecting two jobs to transfer to the destination.

- Options:

If Object Exists – If the Jobs already exist on the destination then you have three choices, first Fail Task execution, Second overwrite the destination job and third skip the existing job and continue with others.

Enable Jobs At Destination – After the transfer you can also specify whether to enable or disable the job.