Script Transform

Hi,

My requirement is to check whether value of a particular column is null or not. if it is null I have to enter warning messages into the temp table I have created.

For this I am using Script Transform

Now I want to know how to write info from script transform to a table using SSIS.

Currently I am using the following code in script component

[Code]

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ConnString As String

ConnString = "Data Source=ABC;Initial Catalog=XXXX;Integrated Security=SSPI;"

Dim sqcn As New SqlConnection(ConnString)

''Dim sqlCmd As New SqlCommand(ConnString, sqcn)

'sqcn.Open()

if Row.Col1_IsNull Then

sqlCmd.CommandText = "Insert into AuditLog values('ERROR','Missing','" + Row.Col5 + "','" + Row.Col6 + "') "

sqlCmd.ExecuteNonQuery()

end if

End Sub

[/Code]

Without using SqlConnection and SqlCommand is thereany way I can get the Connection Obj and able to insert rec in the table.

Thanks

You can create ADO.NET Connection Manager object in the package, get it in script transfrom, and the AquireConnection method returns the managed connection object.

But why do you need script transform at all? You can use conditional split transform to find rows with missing column, and direct the output of conditional split transform to Sql Server or OLEDB Destination.

scope_identity from vwd VB

I have seen plenty of messages about using scope_index by creating parameters using HTML but I would like to do it from my .aspx.vb page.

Does anybody know if this is possible? I have got as far as the code below and get stuck when trying to add a new parameter with direction of output.

Any help would be much appreciated, cheers,

Doug.

Dim

NewPropertyAs SqlDataSource =New SqlDataSource

NewProperty.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings(

"ConnectIt").ToString()

NewProperty.InsertCommand =

"INSERT INTO Test (Name) VALUES (@.Name); SET @.NewID=SCOPE_IDENTITY()"

NewProperty.InsertParameters.Add(NewID, id)

NewProperty.Insert()

Use executescalar instead

NewProperty.InsertCommand ="INSERT INTO Test (Name) VALUES (@.Name); SELECT SCOPE_IDENTITY()"

Response.Write(NewProperty.ExecuteScalar.ToString())

HTH
Regards

|||

Thanks for the rapid response but I get the following error when I try it:

'ExecuteScalar' is not a member of the system.web.UI.WebControls.SqlDataSource

Any idea how I overcome this?

Thanks again,

Doug.

|||

Opps, i'm sorry

I thought you are talking about sqlcommand.

Check the following MSDN

http://msdn2.microsoft.com/en-us/system.web.ui.webcontrols.sqldatasource.inserted.aspx

Regards