November 13, 2008 at 2:00 pm
Can some give me correct Syntex to excute a SQL command with a Script Task?
I have and update Statement sitting in a variable I need execute.
What I have is created a pack per example on the web page
Object Variables, ResultSets, and Foreach Loop Containers
I have pull the String from the object but now I need to excute the update statement I compured..
Try
Dim MySQLString As String = Dts.Variables("AdjustDescription").Value.ToString
Dim sMsg As String
Dim vars As Variables
Dim vars1 As Variables
Dim MyString As String
Dim connName As String = Dts.Connections(0).Name
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)
Dim da As Odbc.OdbcCommand
Try
Dts.VariableDispenser.LockForRead("FileName")
Dts.VariableDispenser.GetVariables(vars)
MyString = vars("FileName").Value.ToString.Substring(0, 37)
sMsg = MySQLString.Replace("@CycleName", MyString)
MsgBox(sMsg)
da = New Odbc.OdbcCommand(sMsg, conn)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
vars1.Unlock()
Dts.TaskResult = Dts.Results.Success
End Try
Catch ex As Exception
Throw ex
End Try
November 13, 2008 at 2:25 pm
I believe you just need to add a:
da.ExecuteNonQuery()
This executes the command and does not expect a resultset to be returned.
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
November 13, 2008 at 2:30 pm
Is this correct don't seam to like it?
Try
Dim MySQLString As String = Dts.Variables("AdjustDescription").Value.ToString
Dim sMsg As String
Dim vars As Variables
Dim vars1 As Variables
Dim MyString As String
Dim connName As String = Dts.Connections(0).Name
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)
Dim da As Odbc.OdbcCommand
Try
Dts.VariableDispenser.LockForRead("FileName")
Dts.VariableDispenser.GetVariables(vars)
MyString = vars("FileName").Value.ToString.Substring(0, 37)
sMsg = MySQLString.Replace("@CycleName", MyString)
MsgBox(sMsg)
da.ExecuteNonQuery(sMsg)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
vars1.Unlock()
Dts.TaskResult = Dts.Results.Success
End Try
Catch ex As Exception
Throw ex
End Try
November 13, 2008 at 2:47 pm
What do you mean by "doesn't seem to like it"?
You still need to instantiate the command object like you were doing, and then you add the code I supplied.
Try something like this (you may need to tweak my syntax):
Try
Dim MySQLString As String = Dts.Variables("AdjustDescription").Value.ToString
Dim sMsg As String
Dim vars As Variables
Dim vars1 As Variables
Dim MyString As String
Dim connName As String = Dts.Connections(0).Name
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)
Dim da As Odbc.OdbcCommand
Try
Dts.VariableDispenser.LockForRead("FileName")
Dts.VariableDispenser.GetVariables(vars)
MyString = vars("FileName").Value.ToString.Substring(0, 37)
sMsg = MySQLString.Replace("@CycleName", MyString)
MsgBox(sMsg)
da = New Odbc.OdbcCommand(sMsg, conn)
If conn.Status <> Open Then
conn.Open
End If
da.ExecuteNonQuery(sMsg)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
vars1.Unlock()
Dts.TaskResult = Dts.Results.Success
End Try
Catch ex As Exception
Throw ex
End Try
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
November 13, 2008 at 3:01 pm
Jack first thanks for your help.
Still have an issue on the following
If conn.Status <> Open Then
conn.Open()
End If
da.ExecuteNonQuery(sMsg)
I get blue line under conn.Status when I hover over it says:
Status not a member of System.Data.odbc.odbcConnetion
also get blue line under Open when I hover it says:
Open in not declared. File I/O Functionality is available Microsoft.VisualBasic NameSpace
Also get blue line under da.ExecuteNonQuery(sMsg) when I hover it says:
Overload Resolution failed because of no accessible 'ExecuteNonQuery ' Accepts this number of Arguments
Think I may be missing some kind of Import at the top???
Again thanks !!!
November 14, 2008 at 6:22 am
Jack Still need your help come on man its Friday we can it!!
Michael
November 14, 2008 at 6:51 am
Okay, as I said in my previous post, you may need to tweak the code. So replace this:
If conn.Status <> Open Then
conn.Open
End If
da.ExecuteNonQuery(sMsg)
With this:
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
da.ExecuteNonQuery()
You should have been able to do what I did, which is use Intellisense to get the correct syntax.
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
November 14, 2008 at 7:07 am
Awesome that fixed those issues.
Now It is blowing chunks on:
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)
Says:
DTS Script Task RunTime Error
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.Odbc.OdbcConnection'. 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.
We are almost there Jack..
November 14, 2008 at 7:48 am
Hey Jack Something I just noticed?
You declare/Dim ConnName but never use it??
Are we missing something?
November 14, 2008 at 7:55 am
Made a couple of changes Jack.
I changed the index of the Dts.Connections(0).
From
Dim connName As String = Dts.Connections(0).Name
To
Dim connName As String = Dts.Connections(1).Name
From
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(1).AcquireConnection(Nothing), Odbc.OdbcConnection)
To
Dim conn As Odbc.OdbcConnection = CType(Dts.Connections(1).AcquireConnection(Nothing), Odbc.OdbcConnection)
STILL HAVE THE ERROR ABOVE HOPPING YOU CAN HELP!!
November 14, 2008 at 8:04 am
Check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3478943&SiteID=1
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
November 14, 2008 at 8:18 am
Just tried this jack still getting errors.
Am I the first to execute a update statement from Script?
There is documention out there but its all over the place.
Some person does it this way other does it other way?
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
Dim conn As OleDb.OleDbConnection
Dim ocmd As IDbCommand
cm = Dts.Connections("OLEDB_NORTHWIND_1")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
ocmd = conn.CreateCommand()
ocmd.CommandText = "select @@version"
ocmd.CommandType = CommandType.Text
MsgBox(ocmd.ExecuteScalar())
'MsgBox(conn.State & conn.ServerVersion)
Dts.TaskResult = Dts.Results.Success
STUFF IN BOLD Say type wrapper not defined??
This don't look good for me? :unsure:
November 14, 2008 at 1:56 pm
Here is the answer I thinks this makes me the expert now!!
Try
Dim MySQLString As String = Dts.Variables("AdjustDescription").Value.ToString
Dim sMsg As String
Dim vars As Variables
Dim MyString As String
Dim connStr As String
connStr = "Hard Coded Connection String"
Dim conn As OleDb.OleDbConnection = New OleDbConnection(connStr)
conn.Open()
Try
Dts.VariableDispenser.LockForRead("FileName")
Dts.VariableDispenser.GetVariables(vars)
MyString = vars("FileName").Value.ToString.Substring(0, 37)
sMsg = MySQLString.Replace("@CycleName", MyString)
MsgBox(sMsg)
Dim myCmd As OleDb.OleDbCommand = New OleDbCommand(sMsg, conn)
myCmd.CommandType = CommandType.Text
myCmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Try
Catch ex As Exception
Throw ex
End Try
November 15, 2008 at 4:21 pm
Just note that there is no point in catching exceptions and then throwing them again without doing anything else.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply