April 30, 2010 at 1:25 am
Hi,
I am migrating SQL server 2000 DTS packages to SSIS packages. I am new to this. I have ActiveX Script Task in my DTS package. Somewhere I read that ActiveX Script task is going to deprecated in future versions, so I have decided to write same using Script Task in SSIS. The code in DTS package is
Function Main()
DIM cn, rs, strQuery
DTSGlobalVariables("gstrCurrentStep").Value = "Extract Server"
DTSGlobalVariables("gstrCurrentTable").Value = "BIW_Server"
Set cn = CreateObject("ADODB.Connection")
cn.Open "DSN=BI_Database;UID=" & DTSGlobalVariables("gstrTargetLogin").Value & ";PWD=" & DTSGlobalVariables("gstrTargetPassword").Value
strQuery = "EXEC BI_LogStart '" & DTSGlobalVariables("gstrCurrentStep").Value & "','" & DTSGlobalVariables("gstrCurrentTable").Value & "'"
cn.Execute(strQuery)
cn.close
set rs = nothing
set cn = nothing
Main = DTSTaskExecResult_Success
End Function
Now I want to execute stored procedure using global/package variables in Script Task. Can anybody provide me sample code.
Thanks.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
April 30, 2010 at 1:54 am
If You just need to Run some procedure, then you can go for Execute SQl Task instead of doing it in the Script Task.
If still need Script Task to achieve the same,please let me know
April 30, 2010 at 2:05 am
Hi Sharath,
Thanks for your response. Yes I want to use Script Task. Can you provide me sample code.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
April 30, 2010 at 4:10 am
Here is the code:
Public Sub Main()
Dim user As String
Dim Password As String
user = Dts.Variables("gstrTargetLogin").Value.ToString()
Password = Dts.Variables("gstrTargetPassword").Value.ToString()
' create and open the connection
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=MyServer;Initial Catalog=SSIS_Testdb;User ID=" + user + ";Password=" + Password + ";"
SQLCon.Open()
'create the command object and execute the procedure
Dim SQLCmd As New SqlCommand
SQLCmd.Connection = SQLCon
SQLCmd.CommandText = "Exec MyProc"
SQLCmd.ExecuteNonQuery()
SQLCon.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Please note that You need to add the variables that you declared to the script task ReadOnlyVariables,ReadWriteVariables list with the coma separation.
You can write any of the VB.Net code into the script editor window.
To connect to the sql server please add the followng name space
Imports System.Data.SqlClient
let me know if any more help is required.
April 30, 2010 at 4:12 am
Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim user As String
Dim Password As String
user = Dts.Variables("gstrTargetLogin").Value.ToString()
Password = Dts.Variables("gstrTargetPassword").Value.ToString()
' create and open the connection
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=MyServer;Initial Catalog=SSIS_Testdb;User ID=" + user + ";Password=" + Password + ";"
SQLCon.Open()
'create the command object and execute the procedure
Dim SQLCmd As New SqlCommand
SQLCmd.Connection = SQLCon
SQLCmd.CommandText = "Exec MyProc"
SQLCmd.ExecuteNonQuery()
SQLCon.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
May 3, 2010 at 3:42 am
Hi Sharath,
Thank you. I have tried with your code.
cn.Open "DSN=BI_Database;UID=" & DTSGlobalVariables("gstrTargetLogin").Value & ";PWD=" & DTSGlobalVariables("gstrTargetPassword").Value
Here "BI_Database" is data source. I'm able to open connection successfully when I use my server name and getting following error when I use data source name. How can I connect to data source in your code.
[p]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)[/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
May 3, 2010 at 7:25 am
Did you add the SQL OLEDB Connection to the Script Task Connection Manager ? Use that connection Manager Name in the script task
May 3, 2010 at 8:15 am
Hi divyanth,
I have created .Net Providers\ODBC Data Provider connection and tried but got failed.
Actually I'm migrating existing DTS packages to SSIS packages. In these existing DTS packages they have used ODBC DSN to get connection. Can I follow the same. Can I create connection using existing DSN..?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply