But before you take note of that code snippet, please be reminded that it only works with Console and Windows applications. This is not applicable with ASP .NET because I tried that approach so many times long long time ago. So, is there any other way? One way is thru the use of xp_cmdshell of MSSQL as indicated in this link à http://www.windowsitpro.com/Article/ArticleID/14277/14277.html. Honestly, I don’t have any idea what are the drawbacks of using either of the techniques. Performance wise, I think using SQLDMO should be the least to be considered. At the same time, unless called by some instances that you really have to use it in .NET for adventure or flexibility sake, it’s too tedious when coding in .NET. Like what I did for the job scheduling, the only self-perceived way is to use it. Otherwise, I will not be using it either if there is still other way.
To begin with, let’s create a DTS Package first. The simplest DTS. Open your MSSQL Server EM to start the adventure. First things first, consider a database to be used. Create a dummy database or as personal discretion can be implied, you can use existing database and create a table called TEMP with just one column, TEMP_COL of type nvarchar(50). Let’s say, I created the table under the Northwind database. Our objective will be, any entry in the ASP .NET must be inserted to the TEMP database following this process:
ASP .NET à SP à XP_CMDSHELL à DTS à TEMP TABLE
1. String Entry from ASP .NET to be passed to Stored Procedure à for instance, “DEMO_DTS” string value
2. Using XP_CMDSHELL within the stored procedure, pass the parameter value to DTS (passing value to DTS Global Variable) and execute the DTS using DTSRUN utility.
3. DTS inserts the value “DEMO_DTS” to the TEMP table
I know that this sample is too silly because it’s too impractical to use XP_CMDSHELL and DTS just to insert a record to the TEMP. We can even do it in .NET without using SP (Stored Procedure). But this is the simplest way to demonstrate the whole process on how to execute DTS Packages from .NET so I guess it’s justifiable J. Now,
(1) Go to your desired server and go to the Data Transformation Services folder.
(2) Right click on it, select New Package
(3) Place the following task objects:
1. ActiveX Script (if prompted, define any name to distinguish the script object)
2. MSSQL Connection object (configure the connection property)
3. Execute SQL Task (Place “SQLROBOT” as the name, select the MSSQL Connection object as the existing connection and indicate the SQL statement. For instance:
INSERT INTO TEMP VALUES('@VAR')
The @VAR value will just serve as a temporary key value and will be replaced dynamically via ActiveX Script Task object.
(4) Select the ActiveX Script object and (using CTRL+Left Click) the Execute SQL Task object.
(5) Go to Workflow > On Success. This is to define the process flow and direct the process in case the script was executed successfully.
(6) To test if the package will produce a positive result, go to Package > Execute. Check the TEMP table is the value @VAR is inserted.
(7) Right click on the design stage, select Package Properties. In this window, you can define DTS description and global variables.
(8) Go to the Global Variables tab, identify one variable. Let’s say, TEMP_VAR then click OK.
(9) Double-click the ActiveX Script Task Object and replace:
'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
End Function
With this one:
'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
On Error Resume Next
Dim objTask
Dim strValue
strValue = DTSGlobalVariables("TEMP_VAR").Value
Set objTask = DTSGlobalVariables.Parent.Tasks(2)
objTask.CustomTask.SQLStatement = Replace(objTask.CustomTask.SQLStatement, "@VAR", strValue)
Set objTask = nothing
If Err.Count = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
If you know how to use VBScript (or VB in general), then this would not be a burden to you. If you are comfortable on how to use Jscript, then it is still possible. To describe the code:
On Error Resume Next
Old-school VB error handling. J
Dim objTask
Dim strValue
VBScript variable declaration has no type implementation.
strValue = DTSGlobalVariables("TEMP_VAR").Value
DTSGlobalVariables object is part of the GlobalVariables collection that can be used to retrieve Global Variables and it’s value defined in the package. TEMP_VAR is the name of the variable that we defined previously. This variable will contain the value from the stored procedure and thru this line, the value will be retrieved and strValue variable will get the value as depicted.
Set objTask = DTSGlobalVariables.Parent.Tasks(2)
DTSGlobalVariables.Parent is used to reference any other objects in the DTS object model hierarchy. Using this object, you can ulitize it to create Connections, Tasks, Steps, etc. We referred the Tasks collection to return the Task object on index of 2, returning a Task type value.
objTask.CustomTask.SQLStatement = Replace(objTask.CustomTask.SQLStatement, "@VAR", strValue)
CustomTask.SQLStatement is a get-and-set property for the SQL Statement. For this sample, get SQL Statement will return the INSERT statement that we defined recently. Replace string function is used to change the @VAR fixed value that we defined to the string value that will be passed from the ASP .NET page.
Set objTask = nothing
If Err.Count = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
DTSTaskExecResult_Success and it’s counterpart are DTS ActiveX Script Constants.
(10) Save the Package as “TEMP_DTS”. Your DTS Package would mostly look like this one:
Now, let’s go to the Stored Procedure. Using your Query Analyzer, select the appropriate database and start creating a stored procedure with one parameter that will accept the string value from ASP .NET. Let say, SPR_ExecutePackage is the name of the stored procedure.
CREATE PROCEDURE SPR_ExecutePackage(
@tempvar nvarchar(200))
AS
BEGIN
END
Declare a variable of type nvarchar that will hold the DTSRUN command string and another variable of type integer that will receive the returned value of the XP_XMDSHELL execution.
DECLARE @rc int
DECLARE @TSQL nvarchar(4000)
Using DTSRUN utility, assign the DTSRUN command to @TSQL variable. DTRUN utility is used to execute packages created in the Data Transformation Services (DTS) section of your server. For more information regarding this utility, refer to this link à http://doc.ddart.net/mssql/sql2000/html/coprompt/cp_dtsrun_95kp.htm.
SET @TSQL = 'dtsrun /S"(local)" /U"sa" /P"sa" /N"TEMP_DTS" /A TEMP_VAR:8=”' + @tempvar + '”'
Given the above statement, /S is for the sername name, /U is for the user id, /P is for the password, /N is for the DTS Package name, /A denotes DTS global variable name, :8 indicates the data type (please see the abovementioned link for the complete list of data types supprted). In the case of more than one global variables, just use the same format (/A).
After setting the dtsrun command in string format, pass the command string to the XP_CMDSHELL just like you’re passing a command string using the EXEC command, catch the returned value and perform some error handling.
EXEC @rc = master.dbo.xp_cmdshell @TSQL
To sum it up, this should be the structure of your stored procedure:
CREATE PROCEDURE SPR_ExecutePackage(
@tempvar nvarchar(50))
AS
BEGIN
DECLARE @rc int
DECLARE @TSQL nvarchar(4000)
SET @TSQL = 'dtsrun /S"(local)" /U"sa" /P"sa" /N"TEMP_DTS" /A TEMP_VAR:8=”' + @tempvar + '”'
--USE /E for trusted connection and remove /U and /P parameters
EXEC @rc = master.dbo.xp_cmdshell @TSQL
IF @rc <> 0
BEGIN
RAISERROR('Error executing DTS. Please contact your system administrator.', 16, 1)
SELECT @rc
END
ELSE
BEGIN
SELECT 0
END
END
Run the stored procedure and try to test if your stored procedure will work.
EXEC SPR_ExecutePackage ‘Testing lng’
Now, let’s proceed with ASP .NET. Create a new ASP .NET web application. Using your default start-up page, add 1 textbox, 1 button and 1 label controls and name them txtSample, btnSample, lblSample respectively. Set the Text property of the textbox and label to null and “Execute” for the button. Begin the coding in the Click event of the button.
VB .NET
-------------
Imports System.Data
Imports System.Data.SqlClient
.......
Private Sub btnSample_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSample.Click
Dim sqlConn As SqlConnection = Nothing
Dim sqlComm As SqlCommand = Nothing
Try
sqlConn = New SqlConnection("User ID=test;Initial Catalog=Northwind;Data Source=(local);Password=test")
sqlConn.Open()
sqlComm = New SqlCommand("SPR_ExecutePackage", sqlConn)
sqlComm.CommandType = CommandType.StoredProcedure
sqlComm.Parameters.Add(New SqlParameter("@tempvar", txtSample.Text))
Dim res As String = sqlComm.ExecuteScalar()
If res = "0" Then
lblSample.Text = "Execution of DTS Package succeeded"
Else
lblSample.Text = "Execution of DTS Package Failed" & "<p>" & res & "</p>"
End If
Catch ex As Exception
lblSample.Text = ex.Message
Finally
If Not sqlComm Is Nothing Then
sqlComm.Dispose()
sqlComm = Nothing
End If
If Not sqlConn Is Nothing Then
sqlConn.Close()
sqlConn.Dispose()
sqlConn = Nothing
End If
End Try
End Sub
C#
-------------
using System.Data;
using System.Data.SqlClient;
.......
private void btnSample_Click(object sender, System.EventArgs e)
{
SqlConnection sqlConn = null;
SqlCommand sqlComm = null;
try
{
sqlConn = new SqlConnection("User ID=test;Initial Catalog=Northwind;Data Source=(local);Password=test");
sqlConn.Open();
sqlComm = new SqlCommand("SPR_ExecutePackage", sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
sqlComm.Parameters.Add(new SqlParameter("@tempvar", txtSample.Text));
string res = sqlComm.ExecuteScalar();
if (res == "0")
{
lblSample.Text = "Execution of DTS Package succeeded";
}
else
{
lblSample.Text = "Execution of DTS Package Failed" + "<p>" + res + "</p>";
}
}
catch (Exception ex)
{
lblSample.Text = ex.Message;
}
finally
{
if (!(sqlComm == null))
{
sqlComm.Dispose();
sqlComm = null;
}
if (!(sqlConn == null))
{
sqlConn.Close();
sqlConn.Dispose();
sqlConn = null;
}
}
}
As can be observed, the code above depicts on how we normally execute stored procedures in .NET. Of course, connection string will vary. Also, on how we catch the exception to be thrown.
That’s it. If you have any comments, suggestions, or additional info that you want to add, feel free to post it.
P.S. @Keith – Sa wakas pre, naka-compose ako ulit ng article.