Execute SQL Task Pragmatically


Dim p As New Package()

Dim cm As ConnectionManager = p.Connections.Add("ADO.NET")

cm.ConnectionString = " Data Source=rajeshsharma\sql2005; Integrated Security=SSPI;Initial Catalog=test;"
Dim e As Executable = p.Executables.Add("STOCK:SQLTask")
Dim th As TaskHost = CType(e, TaskHost)
Dim est As ExecuteSQLTask = CType(th.InnerObject, ExecuteSQLTask)
est.Connection = cm.Name
est.SqlStatementSourceType = SqlStatementSourceType.DirectInput
est.SqlStatementSource = " CREATE TABLE TestTable7 ( ID int NOT NULL, Description nchar(100)); "
'run the package

Dim r As DTSExecResult = p.Execute()
If (r = DTSExecResult.Success) Then

End If

How to get value using SP_EXECUTESQL

DECLARE @Name varchar(50)
DECLARE @Query nvarchar(200)
DECLARE @ParmDefinition NVARCHAR(500)
SET @Query =’Select  @Name=CompanyName  from CompanyDetail’
SET @ParmDefinition = N’@Name varchar(50) OUTPUT’
EXEC SP_EXECUTESQL @Query,@ParmDefinition,@Name=@Name OUTPUT
SELECT @Name