Getting a reference to a Script task in VB.net

I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

Imports Microsoft.SqlServer.Dts.Runtime

But I get errors saying

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?

What am doing wrong?

Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ? = ctype(t.InnerObject,?)

End If

Next

You cannot manipulate properties of objects in the package directly from script within the package. Period.

What you CAN do is put expressions on properties which affect them at execution-time.

-Jamie

|||

I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.

I have figured it out.

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

Module SSISPackage

Public Sub main()

Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"

Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)

Dim cp As ScriptTaskCodeProvider = sct.CodeProvider

Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"

Dim code As String = cp.GetSourceCode(Moniker)

End If

Next

But still does not seem to show the user text added by the developer? Strange.

|||

I had to add a reference to:

Microsoft.SqlServer.ScriptTask

Microsoft.SqlServer.VSAHosting

to my project and then this would work:

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

|||

Ah OK. Sorry, my bad!

Glad you got it working.

-Jamie

sql

Get Value to Return to VB.Net

I have the following stored procedure:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
@.TCoName char(50),
@.TCoAddress char(50),
etc.
@.UserID int OUTPUT,
@.TCoID int OUTPUT
AS
INSERT INTO tblLogin
VALUES
(
@.UserName,
@.Password,
@.EmailAddress
)
Declare @.Ident int
Select @.UserID = @.@.IDENTITY
Select @.Ident = @.UserID
INSERT INTO tblTCompany
VALUES
(
@.Ident,
@.TCoName,
@.TCoAddress,
etc.....
)
Declare @.Ident2 int
Select @.TCoID = @.@.IDENTITY
Select @.Ident2 = @.TCoID
I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I get
the two applications to "talk" to each other?
Any suggestions will be greatly appreciated!
Sandy> I need to grab the @.Ident2 value into VB.Net (for a Web App).
You have a couple of options. One method is to return the value as a result
set:
SELECT @.Ident2
Another technique is to return the value as an OUTPUT parameter:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
etc.,
@.Ident2 OUT
AS
...
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>I have the following stored procedure:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> @.TCoName char(50),
> @.TCoAddress char(50),
> etc.
> @.UserID int OUTPUT,
> @.TCoID int OUTPUT
> AS
> INSERT INTO tblLogin
> VALUES
> (
> @.UserName,
> @.Password,
> @.EmailAddress
> )
> Declare @.Ident int
> Select @.UserID = @.@.IDENTITY
> Select @.Ident = @.UserID
> INSERT INTO tblTCompany
> VALUES
> (
> @.Ident,
> @.TCoName,
> @.TCoAddress,
> etc.....
> )
> Declare @.Ident2 int
> Select @.TCoID = @.@.IDENTITY
> Select @.Ident2 = @.TCoID
> I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I
> get
> the two applications to "talk" to each other?
> Any suggestions will be greatly appreciated!
> Sandy|||Hi Dan -
Thanks for your reply. How do you refer to the value in VB.Net, though?
What's the VB code you would write?
Sandy
"Dan Guzman" wrote:

> You have a couple of options. One method is to return the value as a resu
lt
> set:
> SELECT @.Ident2
> Another technique is to return the value as an OUTPUT parameter:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> etc.,
> @.Ident2 OUT
> AS
> ...
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>
>|||Sandy,
Here's my table definition
CREATE TABLE dbo.Report (
ReportID int IDENTITY (1, 1) NOT NULL ,
Descr varchar (50) NOT NULL ,
ReportName varchar (50) NOT NULL
)
And the associated SP. Notice the @.ReportID as OUTPUT
and the SET @.ReportID as the last statement. That
returns the identity value in the OUTPUT parameter.
---
CREATE PROCEDURE dbo.usp_Report_Ins
@.Descr varchar(50),
@.ReportName varchar(50),
@.ReportID int OUTPUT
AS
INSERT INTO dbo.Report (
Descr,
ReportName
)
VALUES (
@.Descr,
@.ReportName
)
SET @.ReportID = SCOPE_IDENTITY()
This is a snippet of the Insert code. I use the the
Microsoft Data Access Application Block to do the
SQL stuff (SqlHelper statements) to update the DB.
Use whatever code works there. The last statement
retrieves the value of the identity field.
---
Dim params() As SqlParameter = New SqlParameter(2) {}
params(0) = New SqlParameter("@.Descr", Reports.Descr)
params(1) = New SqlParameter("@.ReportName", Reports.ReportName)
params(2) = New SqlParameter("@.ReportID", Reports.ReportID)
params(2).Direction = ParameterDirection.Output
SqlHelperParameterCache.CacheParameterSet(ConnectionSettings.cnString,
_
"usp_Report_Ins", params)
Dim result As Integer =
SqlHelper.ExecuteNonQuery(ConnectionSettings.cnString, _
CommandType.StoredProcedure, "usp_Report_Ins",
params)
dim PrimaryKey as Integer = CInt(params(2).Value)
On Thu, 10 Feb 2005 07:09:02 -0800, "Sandy"
<Sandy@.discussions.microsoft.com> wrote:
>Hi Dan -
>Thanks for your reply. How do you refer to the value in VB.Net, though?
>What's the VB code you would write?
>Sandy
>
>"Dan Guzman" wrote:
>|||larzeb's example shows how you can get a parameter output value. To
retrieve a value returned as a single-row single-column result, you can use
a number of methods, such as SqlCommand.ExecuteScalar,
SqlCommandExecuteReader or SqlDataAdapter.Fill.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:EBDC9678-1001-4809-A0C5-A524115190B0@.microsoft.com...
> Hi Dan -
> Thanks for your reply. How do you refer to the value in VB.Net, though?
> What's the VB code you would write?
> Sandy
>
> "Dan Guzman" wrote:
>

Get Scope Identity Value using ObjectDataSource and Vb.Net

Hi,

I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.

I can't find what event, or how to get the value that the scope identity returns.

Here is my Sproc.

ALTER PROCEDUREdbo.[YourCompany_LanCustomer_Insert]

(

@.DNNUserIDint,

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.Addressnvarchar(50),

@.Address2nvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),

@.Zipnvarchar(50),

@.EmailAddressnvarchar(50),

@.PhoneNumbernvarchar(50),

@.CustomerIDint OUTPUT

)

AS

INSERT INTOYourCompany_LanCustomer

(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)

VALUES(@.DNNUserID, @.FirstName, @.LastName, @.Address, @.Address2, @.City, @.State, @.Zip, @.EmailAddress, @.PhoneNumber,getdate())

SET@.CustomerID =Scope_Identity()

RETURN

When I try to execute the stored procedure in Sql Manager I get the CustomerID Value, how do I get this value in the VB.Net code behind?

Any help is greatly appreciated.

In the Inserted event of the ObjectDataSource and you use the OutputParameters collection to get the value

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs)Dim _customerIdAs Integer =CInt(e.OutputParameters("@.CustomerID"))End Sub

Thanks

-Mark post(s) as "Answer" that helped you

|||

You'll get the collection of the insert parameters for the data source. You can retrieve the value of any parameter using yourDataSourceID.InsertParameters("parameterName"). You haven't explained how you're executing the stored procedure here. If possible post the aspx page code so that we can know how you've setup the data source.

|||

Now I'm lost, do I get the Scope Identity at the iteminserting or the iteminserted. I tried the first sample and get a parameter is not equal error.

Here is my ascx (I'm using dotnetnuke) for the objectDataSource.

<asp:ObjectDataSourceID="ObjectDataSource_Customer"runat="server"TypeName="YourCompany.Modules.Lan.LanCustomerController"SelectMethod="LanCustomer_GetCustomers"DataObjectTypeName="YourCompany.Modules.Lan.LanCustomerInfo"DeleteMethod="LanCustomer_Delete"OldValuesParameterFormatString="original_{0}"InsertMethod="LanCustomer_Insert">

</asp:ObjectDataSource>

here is the code behind (vb.net) that I am using on insert:

ProtectedSub NewItem(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.FormViewInsertEventArgs)Handles CustomerFormView.ItemInserting

Try

e.Values.Item("CustomerId") = 0

If e.Values.Item("DNNUserID") ="-1"Then

e.Values.Item("DNNUserID") ="0"

Else

e.Values.Item("DNNUserID") = UserId

EndIf

If e.Values.Item("FirstName") =""Then

e.Values.Item("FirstName") = Null.NullString

EndIf

If e.Values.Item("LastName") =""Then

e.Values.Item("LastName") = Null.NullString

EndIf

If e.Values.Item("Address") =""Then

e.Values.Item("Address") = Null.NullString

EndIf

If e.Values.Item("Address2") =""Then

e.Values.Item("Address2") = Null.NullString

EndIf

If e.Values.Item("City") =""Then

e.Values.Item("City") = Null.NullString

EndIf

If e.Values.Item("State") =""Then

e.Values.Item("State") = Null.NullString

EndIf

If e.Values.Item("Zip") =""Then

e.Values.Item("Zip") = Null.NullString

EndIf

If e.Values.Item("EmailAddress") =""Then

e.Values.Item("EmailAddress") = Null.NullString

EndIf

If e.Values.Item("PhoneNumber") =""Then

e.Values.Item("PhoneNumber") = Null.NullString

EndIf

Catch exAs Exception

ProcessModuleLoadException(Me, ex)

EndTry

EndSub

The insert sproc worked before, I just can't get it to work now.

|||

Hi,

SET @.CustomerID = Scope_Identity()

From the code you provided, the @.CustomerID is an OUTPUT parameter you set, right?

And we assume that you are using SqlCommand to execute the stored procedure in your business object method, and then you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Thanks.

|||

Nai-Dong Jin - MSFT:

you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Why do we need to "RETURN" the "OUTPUT PARAMETER" ? Do you know that RETURN values and OUTPUT parameters are independent of each other and we could retrieve either "RETURN" value or "OUTPUT" parameter or both of them?

Note: I dont know how the thread was marked as "Answer"

Thanks

-Mark post(s) as "Answer" that helped you

|||

Hi e_screw,

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And since the original poster was using ObjectDataSource, so he must had invoked the ExecuteNonQuery() in the business object method, right? What I said "return the parameter's value" means return the value from the business method. In this stage, that's totally nothing related with the OUTPUT parameter in procs.

Now I'm lost,

Second,of course, you also can use "Return" to achieve that, but since the original poster was lost, kept asking against previous solution and no one followed up, I just provide another solution for him to refer.

So if you are able to help him further with your solution, I appreciate it. And it also can be beneficial to other community members reading the thread.

Thanks.

|||

This is your previous post:

Nai-Dong Jin - MSFT:

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Last post:

Nai-Dong Jin - MSFT:

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

In the first you said, get the value from the ReturnValue property , after assigning the value of OUTPUT parameters to it. In the second, you are just talking about OUTPUT parameters.

Have you had looked at the ObjectDataSourceStatusEventArgs, there is OutputParameters (which returns a collection of output parameters and their values) and a ReturnValue (which gets the return value returned by the business object, if any). Now read your replies again.

Note: Its not with my solution or your solution. Its all about a correct solution, which helps many other community members.

Thanks

|||

Hi,

To Dan5150,

Here's the sample code for you which describes the solution in my previous posts.

First, in your Procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[ProcName]@.TOINSERTNVARCHAR(50),@.RESULTINT OUTPUT-- THE OUTPUT Parameter has been set as OUTPUTAS INSERT INTO MYTABLE(TOINSERT)VALUES (@.TOINSERT)SET@.RESULT = SCOPE_IDENTITY();

Second, here's the method in business object class:

Public Function BusinessMethod(ByVal TOINSERTAs String)As String Dim connAs String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionStringDim myconnAs New SqlConnection(conn)Dim mycommAs New SqlCommand() mycomm.Connection = myconn mycomm.CommandText ="ProcName" mycomm.CommandType = CommandType.StoredProcedureDim sp1As New SqlParameter() sp1.ParameterName ="TOINSERT" sp1.Value = TOINSERTDim sp2As New SqlParameter() sp2.ParameterName ="RESULT"' This parameter has been set in OUTPUT direction sp2.Direction = ParameterDirection.Output sp2.Size = 4 sp2.SqlDbType = SqlDbType.Int mycomm.Parameters.Add(sp1) mycomm.Parameters.Add(sp2) myconn.Open() mycomm.ExecuteNonQuery() myconn.close()' Return the parameter in OUTPUT direction.Return sp2.Value.ToString()End Function

Third, you can get the value in Inserted event of ODS by accessing ReturnValue property.

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue.ToString())' You can get the id of new inserted row here.End Sub

To e_screw,

Please read my codes, and especially the comment parts in bold. And let's back to your solution which given in the second post:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim _customerId As Integer = CInt(e.OutputParameters("@.CustomerID"))
End Sub

You can use OutputParameters collection to retrieve the value, while output parameters would be ByRef (out in C#) parameters.

But since the original poster hadn't posted out his business method signature, how can you make sure that he was declaring parameters that are passed to the business object method by reference? If the parameters was passed by val, how could he get the value in OutputParameters collection?

Thanks.