Category Archives: SQL Server

Deploying SSIS Packages

Posted on by
15

In this article we shall take a look at the steps in building an SSIS Package and deploy it.

You may have designed the package in your Development System and tested the Package from BIDS. Now it’s time to move it to the SQL Server. Up to this point, you have developed a Package in BIDS but have not deployed it onto the Integration Services. Let’s look into the steps involved in moving it to SQL Server Integration Services.

Deploying SSIS Packages would be helpful when you have multiple packages in a single solution, so that you can deploy all of them at once.

Building the Solution / Packages

Once the packages are all developed, right click on the Solution in Solution Explorer and choose properties in the Menu. Upon selecting properties, a Dialog box appears as shown in the picture below.

The output path is by default bin, it is the directory where the files that are built are going to be placed by SQL Server.  For deploying the SSIS Packages we need to have deployment files which will not be created by default when you build a project, to enable this under the configuration properties in the left Pane, select Deployment utility as shown in the picture below and change the property value to True corresponding to CreateDeploymentUtility, additionally you can also set the Directory path where the deployment utility has to be placed upon building the solution. Choose your settings and click Apply to ensure the changes in the properties are saved.

The Next step is to Right Click on the Solution in the Solution Explorer and choose Build. (as shown in the picture below)

The status of the build process is showed at the bottom left corner of the BIDS as shown below.

When the build /rebuild is successful, navigate to the directory is referred in the DeploymentOutputPath couple of screens back. You should be seeing the packages in the solution and along with them an additional file usually of 1 KB which is the Manifest File what helps in deploying the packages in the solution.(Picture below)

Deploying the Packages

Double click the Manifest File to start the deployment. The Package Installation wizard begins and Deploy SSIS Packages step is the first screen that is presented.

This screen lets you select where shall the packages be deployed, as mentioned in the Dialog Box, deploying in SQL Server is more secure, since SQL Server stores the packages internally compared to File System where additional security measures needs to taken to secure the physical files. My personal choice would be to deploying inside SQL Server.

Additionally, choose the option to validate the packages after Installation.

If the deployment is on the same Server you can mention “.” in the Server Name or choose the Server Name from the drop down list if is a remote Server where you would like to deploy the packages. Choose the Authentication mode that you would like to connect to that Target Server you want to deploy. Click Next to continue.  

The dialog box above displays, the location where the Package dependencies are going to be stored and can be modified if needed. Click Next after deciding the location for dependencies.

Click Next in the above screen to deploy the packages. The Installation is done and the tasks in the packages are validated.  

Click on Next in Dialog Box and you should be able to see a View Installation details Dialog. Click Finish to complete the Deployment of the SSIS Package.

One of the next steps would be to schedule the Package using SQL Server Agent.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Introduction to Database Snapshots in SQL Server 2005

Posted on by
Reply

Database Snapshots is a new feature introduced in SQL Server 2005. It provides a mechanism that enables us to save an exact read only copy of a database when the snapshot was taken. It would be highly beneficial that a database snapshot is taken when certain bulk changes to the table data is made or you need to maintain an exact copy of your database as it was at the end of the day, each day.

Some points to be noted while working with Database Snapshots.

Database snapshot can be created using T-SQL script only, it cannot be implemented using the Management Studio.

  • Database snapshot of a database should exist on the same SQL Instance as the Source Database. Why this is so would be beyond the point of discussion for this article, so I would explain in another blog soon.
  • Database snapshots cannot be backed up. (But in the Object Explorer you will see that the menu options are available to backup)
  • Database snapshot cannot be updated or modified (That is why it is referred as read only Copy)
  • It is available only in Enterprise Edition of SQL Server 2005.

Script to create the Source Database

USE [master]
GO

CREATE DATABASE [Snapshot_Source] ON PRIMARY

( NAME = N'Snapshot_Source_Data', FILENAME =
N'D:\Snapshot_Source_Data.mdf' , SIZE = 4096KB,

MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Snapshot_Source_log',
FILENAME = N'D:\Snapshot_Source_Log.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

Creating table called DemoTable using the following script

CREATE TABLE [Snapshot_Source].[dbo].[DemoTable](

[Name] [varchar](50) NULL,

[Age] [int] NULL )

ON [PRIMARY]

Inserting records in the DemoTable

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'A' , 30 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'B' , 35 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'C' , 37 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'D' , 40 )

Run the following script in the Query Editor

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

CREATE DATABASE SNAPSHOT_01OCT2009_6AM ON PRIMARY

( NAME = N'Snapshot_Source_Data',

FILENAME = N'D:\SNAPSHOT_01OCT2009_6AM.ss' )

AS SNAPSHOT OF Snapshot_Source;

GO

Modifying the Data in the Source Database

Let’s modify the data in the source database by the following update statements.

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 50 WHERE [Name] = 'A'

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 55 WHERE [Name] = 'B'

Verifying the data at the source database and also from the snapshot by running the following script in the Query Editor.

SELECT * FROM [SNAPSHOT_01OCT2009_6AM].[dbo].[DemoTable]

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

You can use a database snapshot in situations which needs a continuous read only copy of the database at a point of time. For example, you have a reporting application which requires data to be available and it is ok to have the data as it was at the end of day yesterday. A scheduled database snapshot of your transaction based Database which runs after the working hours each day would be a good solution.

Dropping a Database Snapshot

Dropping a database snapshot is same as dropping a database, using the Drop Database command.

The following command would delete the database snapshot that was used in this example.

DROP DATABASE SNAPSHOT_01OCT2009_6AM

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty