Execute SQL statement via OLEDB connection inside Script Task
-
I've been trying to research this but I can't quite follow the web snippets, many of which refer to ADO connections or vague references to namespaces that I'm not sure how to use.
I have a defined OLEDB connection manager in my SSIS package that refers to a sql server. Inside my script task, I need to execute a SQL statement. how can I do this?
-
What are you trying to do with your SQL statement?
-
Could be executing an Update or a Delete, as required.
In this case I found the solution! I didn't actually realize just how easy it was to convert my OLEDB connection manager to ADONET...so I went ahead and did that, and then used these 2 imports statements:
Imports Microsoft.SqlServer.Dts.RuntimeImports System.Data.SqlClientThen:
Dim sqlConn As System.Data.SqlClient.SqlConnectionDim sqlComm As System.Data.SqlClient.SqlCommandDim strSQL As String = ""Dim cm As ConnectionManager = Dts.Connections("ADONETConnection")Then:
[...build up SQL string]
Then:
sqlComm = New System.Data.SqlClient.SqlCommand(strSQL, sqlConn)sqlComm.ExecuteScalar() -
I believe that there's an SQL Task, isn't there? Why not just skip the proverbial tower of Babel and call a stored procedure?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For sure, and I usually do that. This is in a situation where the preference (for whatever reason) is toward dynamic sql statement. And usually the reason for preferring that, is due to needing to loop through [an array, for instance] and execute multiple sql statements BEFORE exiting the script task.
Sure, you could split it out into a bunch of small stored procedures and a whole bunch of execute sql tasks...but there are those who think of that as the long way around. (me included)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply