February 6, 2009 at 9:14 am
Hi guys
Pre-warning: I'm a SSIS noob
I'm looking for a bit of help. I'm trying to take a data set which I have got from a XML file, it consists of multiple rows. What I'm trying to do is add on a service ID onto the data set and then stick it into the DB. The service id is passed into the script component as a variable. I've using the script component as a trasnformation
My Script component looks like this:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub MyInput_ProcessInputRow(ByVal Row As MyInputBuffer)
MyOutputBuffer.AddRow()
MyOutputBuffer.DDI = Row.DDI
MyOutputBuffer.Alias = Row.Alias
MyOutputBuffer.Answered = Row.Answered
MyOutputBuffer.Released = Row.Released
MyOutputBuffer.CallerID = Row.CallerID
MyOutputBuffer.PostCode = Row.PostCode
MyOutputBuffer.Region = Row.Region
MyOutputBuffer.HoldTime = Row.HoldTime
MyOutputBuffer.KeyPad = Row.KeyPad
MyOutputBuffer.serviceID = Variables.serviceID
End Sub
Public Overrides Sub CreateNewOutputRows()
MyBase.CreateNewOutputRows()
End Sub
End Class
Once thats done, I use the output and insert the values into the DB. Problem is, once the script is run, the values are not being put into the DB and I have no idea why they aren't going in.
Any ideas?
February 6, 2009 at 11:14 am
Is your variable declared in the ReadVariables property of the script dialog?
Also, the Derived Column transform is probably a bit easier to use if all you are doing is adding a column with a relatively simple computation, or variable in your case. I generally resort to the Script transform only when I have something unusual or complex that requires a bit of coding to accomplish, because it usually requires several trips to BOL and a bit of debugging before I get it right. The Derived Column is much easier to use. The only thing that tripped me up in the beginning was that it is case sensitive.
February 9, 2009 at 3:47 am
Awesome, thats exactly what I was looking for.
February 9, 2009 at 10:03 am
I've got another problem that is literally causing me to tear my hair out at the moment.
If I try and pass in more than one variable into the Script Component, I get the following error:
The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.
It does not matter which variable I use. For instance, orginally my ReadOnlyVariables look like this:
ReadOnlyVariables - User::serviceBookmark
Then I added a variable:
ReadOnlyVariables - User::serviceBookmark, User::serviceID
I then removed the old variable and kept in the new one:
ReadOnlyVariables - User::serviceID
And that works, so it's not down to the actual variable, but more to the fact thats there's more than one. Now I'm only having this problem with the Script Component (Data Task), seems to be fine with the Script Task (Control Flow). The variables are in scope, but we've already established that since it works for both vars when they are used alone.
Any help greatly appreciated.
G
February 9, 2009 at 10:26 am
I don't know if this matters...
but you might try losing the User:: and just use the variable name
also if there is a space after the comma that might be a problem.
Here is an MS link re: Script Component variables
February 9, 2009 at 10:27 am
Ed (2/9/2009)
I don't know if this matters...but you might try losing the User:: and just use the variable name
also if there is a space after the comma that might be a problem.
Here is an MS link re: Script Component variables
Okay its the space. This wasn't a issue in the other Script Editor which is quite silly.
Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply