February 21, 2009 at 7:12 am
you were right, I review my connection and change it by .net provider and works !!
thanks a lot
March 24, 2009 at 9:55 am
I am totally new to VB.NET. I have stolen the code in this string to execute a stored proc from a Script Component, but I'm there are two things I don't understand.
What do I need to enter where this code has "g_DBAgilSourceOleDBWin"? Is this suppoed to be the name of my Connection Manager?
Also, I get the squiggly underline under Dts. with an message "Name 'DTS' is not declared". What do I need to do to fix that?
Dim mConn As SqlClient.SqlConnection = DirectCast(Dts.Connections("g_DBAgilSourceOleDBWin").AcquireConnection(Nothing), SqlClient.SqlConnection)
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
cmd.Connection = mConn
cmd.CommandText = "myStoredProc Parm1, Parm2"
cmd.CommandType = CommandType.StoredProcedure
'cmd.Parameters.AddWithValue("Parm1", DbType.String).Value = "SLS"
'cmd.Parameters.AddWithValue("Parm2", DbType.String).Value = "Cat"
cmd.ExecuteNonQuery()
Thanks. Any help is appreciated!
March 24, 2009 at 10:58 am
"g_DBAgilSourceOleDBWin" should be the name of your connection manager.
Someone else had a question and posted their code. I responded back using whatever name they were using for their connection.
If you are getting a squiggly line under "Dts" then I think you are missing the directive "Microsoft.SqlServer.Dts.Runtime"
Make sure you have at the top of your script the following:
Imports Microsoft.SqlServer.Dts.Runtime
Also where you are setting the 'CommandText' it should be just the name of the stored procedure you are calling.
(e.g. cmd.CommandText = "myStoredProc")
If you have parameters, set each one like the following:
cmd.Parameters.AddWithValue("Parm1", DbType.String).Value = "SLS"
March 24, 2009 at 11:41 am
Thanks for the quick response!
I added "Imports Microsoft.SqlServer.Dts.Runtime" and when that didn't fix the problem I also added "Imports Microsoft.SqlServer.Dts.Pipeline" to see if that would fix it. I'm still getting the squiggly line and these errors:
Error1Validation error. Data Flow Task - ImportPendPolicy to Web Tables: Script Component - Lookup in PID [312]: Error 30451: Name 'Dts' is not declared. Line 90 Column 59 through 61 Error 30451: Name 'Dts' is not declared. Line 103 Column 9 through 11ImportLifePending.dtsx00
Error2Validation error. Data Flow Task - ImportPendPolicy to Web Tables: Script Component - Lookup in PID [312]: Error 30451: Name 'Dts' is not declared. Line 90 Column 59 through 61 Error 30451: Name 'Dts' is not declared. Line 103 Column 9 through 11ImportLifePending.dtsx00
Error3Validation error. Data Flow Task - ImportPendPolicy to Web Tables: Script Component - Lookup in PID [312]: The script component is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Component Editor by clicking Design Script button to cause binary code to be generated.ImportLifePending.dtsx00
Your help is greatly appreciated!
Sandy
March 24, 2009 at 12:00 pm
I think the problem is that you are using a "script component" from the "Data Flow" tab and I'm referring to a "script task" from the "Control Flow" tab.
March 24, 2009 at 12:22 pm
You're absolutely right that I'm using a Script Component in Data Flow, not a Script Task in Control Flow. Sorry about not noticing the difference in the thread.
Does anyone know if I can execute a stored proc from a Script Component in Control Flow?
March 24, 2009 at 12:40 pm
I'm not sure what you are trying to do, but you can execute a stored procedure
in the "Data Flow" tab by using a "OLE DB Source" control and then in the "SQL command text" window type:
exec [dbo].[MyStoredProcedure]
Are you trying fetch a recordset from SQL and then loop through
the records with some biz logic?
March 24, 2009 at 1:22 pm
I bring data from a Flat File directly into a Staging table (no changes or validation done on the data at all). I want the staging table to have all the rows so I can give access to it to the mainframe developers so they can easily query against it to clean up the data they are sending to SQL.
Below is the Script Component I have written... It is basically code I stole (and changed a little) from a recent SQLServerCentral article (SSIS Custom Error Handling by Zach Mattson on 03/17/09). In my Data Flow, I redirect any rows with a problem in a Lookup or Data Conversion to a Script Component like this. It then flows to an SQL error table. The reason for the stored proc is I want to update a SkipInsertFlag column in the staging table so I know that I do not want to attempt to insert a row in the permanent table if a problem was found.
I will write a different stored proc to do the insert/update to the permenant tables from a Control Flow Execute SQL task. But this stored proc needs to know which rows had a problem found in the Data Flow so an Insert or Update is not attempted.
Any ideas or am I just going about this all wrong? This is only my third SSIS package, and I don't know how I make it so darn complicated.
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
'Public Class ScriptObjectModel
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
Dim rowType As Type = Row.GetType()
Dim columnValue As PropertyInfo
Dim strErrorDetails As String = " <field"
Dim strAttributeName As String
Dim strValue As String
Dim sql As String
'Dim Dts As String
Dim PolicyNbrParm As String 'PropertyInfo 'Variable to feed into stored proc.
Row.ErrorCodeDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim() 'Get the error description
Row.ErrorStep = ComponentMetaData.Name.Trim() 'Get the script/component name
Row.PackageName = Variables.PackageName.Trim() 'Get the package name
Row.PackageStartDateTime = Variables.StartTime 'Get the package start date/time
Row.PackageUserName = Variables.UserName 'Get the package user name
Row.ErrorTask = Variables.TaskName.Trim() 'Get the task name
Try
' loop through the columns in the input to create a generic xml doc
For Each column In ComponentMetaData.InputCollection(0).InputColumnCollection
' remove illegal xml characters in the name
If column.Name.IndexOf(".") > -1 Then
strAttributeName = column.Name.Replace(".", "")
Else
strAttributeName = column.Name
End If
If strAttributeName.IndexOf("_") > -1 Then
strAttributeName = strAttributeName.Replace("_", "")
End If
If strAttributeName.IndexOf(" ") > -1 Then
strAttributeName = strAttributeName.Replace(" ", "")
End If
' Skip the Error Columns
If Not (strAttributeName.Contains("ErrorCode") Or strAttributeName.Contains("ErrorColumn")) Then
' get the value for the column
columnValue = rowType.GetProperty(strAttributeName)
Dim objColumn As Object = columnValue.GetValue(Row, Nothing)
' if the value is null set to empty string
If IsNothing(objColumn) Then
strValue = String.Empty
Else
strValue = objColumn.ToString()
End If
' append the node to the xml string
strErrorDetails = strErrorDetails & " name=" & _
ControlChars.Quote & column.Name.Trim & ControlChars.Quote & _
" value=" & ControlChars.Quote & strValue & ControlChars.Quote & _
"/><field"
If (strAttributeName.Contains("PolicyNbr")) Then ' Get the PolicyNbr as a parameter...This is working!!!!
PolicyNbrParm = strValue
End If
End If
Next
'finish the xml string
strErrorDetails = strErrorDetails.Substring(0, strErrorDetails.Length - 7) & _
"> "
Catch ex As Exception
' if an error occurs log it and keep processing instead of killing the load
strErrorDetails = " <field name=" & ControlChars.Quote & "error" & _
ControlChars.Quote & " value=" & ControlChars.Quote & ex.ToString.Trim & _
" " & strAttributeName.Trim & ControlChars.Quote & "/> "
End Try
'add the xml output to the output row
Row.ErrorDetails = strErrorDetails
'Set the PolicyNbrOut so I know it works...This can be commented out later!
Row.PolicyNbrOutput = PolicyNbrParm
'Run stored proc to update SkipInsertFlag in Import table
Dim mConn As SqlClient.SqlConnection = DirectCast(Dts.Connections("ConnectionMgr").AcquireConnection(Nothing), SqlClient.SqlConnection)
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
cmd.Connection = mConn
cmd.CommandText = "Import_UpdateSkipInsertFlag"
'Need to use the next line when I'm ready to start sending parameters to stored proc.
'cmd.CommandText = "Import_UpdateSkipInsertFlag TableName, PolicyNbr"
cmd.CommandType = CommandType.StoredProcedure
'Need to use the following lines to define parameters.
'cmd.Parameters.AddWithValue("TableName", DbType.String).Value = "dbo.ImportPendPolicy"
'cmd.Parameters.AddWithValue("PolicyNbr", DbType.String).Value = PolicyNbrParm
cmd.ExecuteNonQuery()
'Release the connection
'mConn.Close()
'mConn.Dispose()
Dts.Connections("ConnectionMgr").ReleaseConnection(Nothing)
End Sub
End Class
June 12, 2009 at 11:30 am
Here's the code I used ... You will need a Script Transform and an ADO.NET connection. In your script component (Connection Manager), add a connection to your ADO.NET source (by default, its "Connection") . You will, of course, have to substitute your SP and Parameters as necessary.
---------------------------------
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim SqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim SqlParam1 As SqlParameter
Dim SqlParam2 As SqlParameter
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.Connection 'this should be the connection you added
SqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
sqlCmd = New SqlCommand("exec phase1_BRDSource_Update_sp @vendor, @type", SqlConn)
SqlParam1 = New SqlParameter("@vendor", SqlDbType.VarChar)
sqlCmd.Parameters.Add(SqlParam1)
SqlParam2 = New SqlParameter("@type", SqlDbType.VarChar)
sqlCmd.Parameters.Add(SqlParam2)
'MyBase.AcquireConnections(Transaction)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim type As String
type = Row.ExcelTable().ToString().Replace("$", "").ToUpper()
sqlCmd.Parameters("@vendor").Value = Row.Vendor().ToString()
sqlCmd.Parameters("@type").Value = type
sqlCmd.ExecuteNonQuery()
ParameterDirection.Output
End Sub
End Class
--------------------------------------
January 21, 2010 at 12:38 pm
Hi,
Do you have examples in C# to call a store procedure in a script task?
Thanks
January 21, 2010 at 2:49 pm
Sorry. No I do not.
January 21, 2010 at 3:07 pm
This is not perfect as I don't have SSIS 2008 on at work, but if you take the code, not including the procedure/method signatures you should be well on your way:
using System;
using System.Data;
using System.Math;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
Public Class ScriptMain
Inherits UserComponent
{
IDTSConnectionManager90 connMgr;
SqlConnection SqlConn;
SqlCommand sqlCmd ;
SqlParameter SqlParam1;
SqlParameter SqlParam2;
Public Overrides Sub AcquireConnections(object Transaction)
{
connMgr = this.Connections.Connection; // this should be the connection you added
SqlConn = (SqlConnection)connMgr.AcquireConnection(Nothing);
sqlCmd = new SqlCommand("exec phase1_BRDSource_Update_sp @vendor, @type", SqlConn);
SqlParam1 = new SqlParameter("@vendor", SqlDbType.VarChar);
sqlCmd.Parameters.Add(SqlParam1);
SqlParam2 = new SqlParameter("@type", SqlDbType.VarChar);
sqlCmd.Parameters.Add(SqlParam2);
//MyBase.AcquireConnections(Transaction)
}
Public Overrides Sub Input0_ProcessInputRow(Input0Buffer Row)
{
string type;
type = Row.ExcelTable().ToString().Replace("$", "").ToUpper();
sqlCmd.Parameters("@vendor").Value = Row.Vendor().ToString();
sqlCmd.Parameters("@type").Value = type;
sqlCmd.ExecuteNonQuery();
ParameterDirection.Output;
}
}
There are likely some syntax errors in the code, but like I said, it should get you started.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2010 at 8:41 am
Hi Jack,
Thanks for your help. I am still learing the C# language. On the deflaut script task in C# where does code fit into below. The code in red is from you but I do not think I am doing it right. Also I have an OLEDB configuration file name ABCConnection. How do I call it on your connection string
connMgr = this.Connections.Connection; is it connMgr = this.Connections.ABCConnection;
Please advise. Thanks.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Math;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
namespace ST_22763bdd02604c9897f02329d21753d5.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
// TODO: Add your code here
Public Class ScriptMain
Inherits UserComponent
{
IDTSConnectionManager90 connMgr;
SqlConnection SqlConn;
SqlCommand sqlCmd ;
SqlParameter SqlParam1;
SqlParameter SqlParam2;
Public Overrides Sub AcquireConnections(object Transaction)
{
connMgr = this.Connections.Connection; // this should be the connection you added
SqlConn = (SqlConnection)connMgr.AcquireConnection(Nothing);
sqlCmd = new SqlCommand("exec phase1_BRDSource_Update_sp @vendor, @type", SqlConn);
SqlParam1 = new SqlParameter("@vendor", SqlDbType.VarChar);
sqlCmd.Parameters.Add(SqlParam1);
SqlParam2 = new SqlParameter("@type", SqlDbType.VarChar);
sqlCmd.Parameters.Add(SqlParam2);
//MyBase.AcquireConnections(Transaction)
}
Public Overrides Sub Input0_ProcessInputRow(Input0Buffer Row)
{
string type;
type = Row.ExcelTable().ToString().Replace("$", "").ToUpper();
sqlCmd.Parameters("@vendor").Value = Row.Vendor().ToString();
sqlCmd.Parameters("@type").Value = type;
sqlCmd.ExecuteNonQuery();
ParameterDirection.Output;
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
September 29, 2010 at 12:02 am
Sorry to bring this up again, but I'm trying the above in SSIS 2005, and am getting the following error
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim dtResult As New DataTable
Dim row As System.Data.DataRow
Dim sSql As String
Dim sTableName As String
Dim connMgr As ConnectionManager = Dts.Connections("ImpactAnalysis")
Dim conn As SqlClient.SqlConnection
Dim strConnection As String = connMgr.ConnectionString
Dim sSourcePath As String = Dts.Variables("SourcePath").Value.ToString
Dim command As SqlClient.SqlCommand
Dim strReader As SqlClient.SqlDataReader
Dim xslString As String
' This works
Try
connMgr.AcquireConnection(Nothing)
Dts.Events.FireInformation(1, "", "Connection successfully acquired " + connMgr.Name, "", 0, False)
Catch ex As Exception
Dts.Events.FireInformation(1, "", "Connection not acquired " + connMgr.Name, "", 0, False)
End Try
' This doesn't - falls over with the above error.
Dim mConn As SqlClient.SqlConnection = DirectCast(Dts.Connections("ImpactAnalysis").AcquireConnection(Nothing), SqlClient.SqlConnection)
September 29, 2010 at 8:47 am
make sure you have added the COM objects to the SSIS from your .NET framework library. It should be on the SSIS Design tools somewhere.
Thanks.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply