SAVE DTS TO DATABASE

Introduction:

To start the analysis or development activity for any projects involving DTS packages, the latest DTS packages are taken from production and saved in the development environment’s file system. Each of these DTS packages needs to be opened manually through the database and saved internally in the database. This becomes a hectic task if there is huge number of DTS packages to be saved prior to the analysis activity.

The “SAVE DTS TO DATABASE” application saves all the selected DTS packages into the database at one click of a button if sufficient privileges are provided to the ASPNET account in the database.

Components:

This application consists of 2 parts:

1. Back end: consisting of the stored procedure, which does the task of saving the packages, saved in the database where the packages are to be saved.
2. Front end: designed using asp.net and C Sharp.

Back end:

This consists of a stored procedure “s_LoadPackageToServer” stored in SQL Server 2000 master database. This SP takes the following 2 arguments to save a package into its server:

1. File Name (Complete path of the DTS package as saved in the file system)
2. Package Name (The package name with which the DTS package is to be saved in the DB).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create procedure s_LoadPackageToServer
@PackageName varchar(128) ,
@FileName varchar(500)
as
/*
exec s_LoadPackageToServer
@PackageName = 'Package2' ,
@FileName = 'c:\Masood\Package1.dts'
*/
--declare @PackageName varchar(128)
--set @PackageName = 'Package1'

--declare @FileName varchar(500)
--set @FileName = 'c:\Masood\Package1.dts'

-- declare @Username varchar(100)
-- declare @Password varchar(100)

declare @objPackage int
declare @rc int
set @rc = 1

exec @rc = sp_OACreate 'DTS.Package', @objPackage output
if @rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @rc)
return
end
else
print 'Package Created'

exec @rc = sp_OAMethod @objPackage, 'LoadFromStorageFile' , null,
@UncFile = @FileName, @password = null
if @rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
return
end
else
print 'Package Loaded'

exec @rc = sp_OAMethod @objPackage, 'SaveToSQLServerAs' , null,
@NewName = @PackageName, @ServerName = @@ServerName, @Flags = 256

if @rc <> 0
begin
raiserror('failed to Save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
return
end
else
print 'Package Saved'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Front End:

The front end of this application consists of asp.net page which takes the input values from the user like:

1. Folder Name (Path where the DTS packages reside in the file system)
2. Server Name (Server name where the DTS packages are to be saved)
3. Package List (Select packages from the folder path which are to be saved)

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

//using System.Windows.Forms;

namespace WebApplication1
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.ListBox ListBox1;
protected System.Web.UI.WebControls.ListBox ListBox2;
protected System.Web.UI.WebControls.Button Button2;
protected System.Web.UI.WebControls.Button Button3;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.TextBox TextBox3;
protected System.Web.UI.WebControls.Label Label1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Button2.Click += new System.EventHandler(this.Button2_Click);
this.Button3.Click += new System.EventHandler(this.Button3_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void Button1_Click(object sender, System.EventArgs e)
{
try
{
if(TextBox1.Text.ToString().Equals(""))
{
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('Folder name text box is empty');</script>");
}
else
{
ListBox1.Items.Clear();
String FolderName = TextBox1.Text;
if (!Directory.Exists(FolderName))
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('Directory Does not exist.');</script>");
else
{
if(!FolderName.EndsWith("\\"))
TextBox1.Text = TextBox1.Text.ToString() + "\\";
string[] Filenames = Directory.GetFiles(FolderName);

foreach (string str in Filenames)
if(str.EndsWith(".dts"))
ListBox1.Items.Add(str.Substring(str.LastIndexOf("\\") + 1));
}
}
}
catch(Exception exp)
{
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('Error in getting the package names from the specified folder.');</script>");
}
}

private void Button2_Click(object sender, System.EventArgs e)
{
ListBox2.Items.Clear();
for(int i =0;i <ListBox1.Items.Count ;i++)
if (ListBox1.Items[i].Selected==true)
{
ListBox2.Items.Add(ListBox1.Items[i].Text.Substring(0,ListBox1.Items[i].Text.Length - 4)) ;
}

}

private void sqlConnection1_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{

}

private void Button3_Click(object sender, System.EventArgs e)
{
SqlConnection con = null;
SqlCommand StoredProcedureCommand = null;
try
{
if((ListBox2.Items.Count == 0) || (TextBox3.Text.ToString().Equals("")))
{
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('No Packages Selected or No server name specified.');</script>");
}
else
{
String ServerName = TextBox3.Text.ToString();
String SqlConnStr = "Server=" + ServerName + ";Database=master;Trusted_Connection=Yes;";
//Page.RegisterStartupScript("Testing", "<script language=javascript>alert('"+ SqlConnStr +"');</script>");
con = new SqlConnection(SqlConnStr);
StoredProcedureCommand = new SqlCommand("dbo.s_LoadPackageToServer", con);
StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
con.Open();
SqlParameter myParm1 = StoredProcedureCommand.Parameters.Add( "@PackageName", SqlDbType.Char, 100);
SqlParameter myParm2 = StoredProcedureCommand.Parameters.Add("@FileName", SqlDbType.Char, 100);

for(int i =0;i <ListBox2.Items.Count ;i++)
{

ListBox2.SelectedIndex = i;
myParm1.Value = ListBox2.Items[i].ToString();
myParm2.Value = TextBox1.Text.ToString() + ListBox2.Items[i].ToString() + ".dts";
StoredProcedureCommand.ExecuteNonQuery();
}
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('All Packages Saved.');</script>");
}
}
catch(Exception exp)
{
Page.RegisterStartupScript("Testing", "<script language=javascript>alert('Either the package " + ListBox2.SelectedItem.ToString() + " already exists or there is an error in saving the packages or theres is a problem in reaching the server. Only first " + ListBox2.SelectedIndex.ToString() + " Items were saved. Please contact your system administrator.');</script>");
}
finally
{
if(StoredProcedureCommand != null)
StoredProcedureCommand.Dispose();
if(con != null)
con.Close();
}

}
}
}

Assigning Permissions: Permissions needs to be granted to the ‘DomainName\ASPNET’ (New Login) account for it to access the SP stored in the DB and execute it. The Following SP’s also need execute permission for the above mentioned DB user:

1. sp_OACreate
2. sp_OAMethod

These SP’s are used internally to create new DTS packages and save the package in the DB.

Look and Feel of UI:

The asp.net page is developed in Visual Studio.NET 2003 and is programmed in C Sharp.NET.

Enter the folder name where the DTS packages exist. If its not a valid folder the page will display an alert that no such folder exists.

On Clicking the “Load Packages List”, all the DTS packages stored in the specified folder gets listed. Then the packages that are to be saved can be selected from the list and clicked on “Show selected Packages List” Button.

The server name is to be specified the last textbox. If no server name is specified, an alert will be displayed:

After entering all the required details, “Load and Save Packages” button is clicked. If all the packages are saved successfully, then a pop up box is displayed about the success message.

If there is an error in saving the nth package then the first (n-1) packages are saved in th DB and an error message is displayed. So these first (n-1) packages should not be selected for loading into the DB the next time as they have already been saved.

Known Constraints and Issues with the application:

1. Text annotations in the DTS packages are not saved.
2. The look of the DTS package changes though the flow and functionality remains the same.

Configuration issues while developing the application:

Aspnet registration:

In command prompt:

C:\Documents and Settings\p111111>cd C:\winnt\microsoft.net\framework\v1.1.4322

C:\WINNT\Microsoft.NET\Framework\v1.1.4322>aspnet_regiis -ir

Start installing ASP.NET (1.1.4322.0) without registering the scriptmap.

Finished installing ASP.NET (1.1.4322.0) without registering the scriptmap.

C:\WINNT\Microsoft.NET\Framework\v1.1.4322>

New project creation failed as the asp.net version was not defined: This can be defined from Internet information Services “inetmgr” from run command

.