Variable issue in script component
-
This seems like it should be really simple but its not working so I'm wondering what I'm doing wrong.
I have a package which has a variable declared called InsurersToRun. Its a simple string.
Im using a script compontent as a source in a data flow. All I want to do is set a variable created in in the script compontent = to the package variable:
Dim mytest As String = Me.Variables.InsurersToRun
Really basic.. but its not working. Error I get is:
The collection of variables locked for read and write access is not available outside of PostExecute.
Note: The variable is listed in the ReadWriteVariables custom properties section of the script component
Anyone know what I'm doing wrong here?
Thanks,
Strick
-
Hi,
I just checked another package that I'm doing the exact same thing in and it works just fine! So now I'm really lost as to what I'm doing wrong
Here is the statement in the other package which works just fine:
Dim SourceFileLocation As String = Me.Variables.ExtractFullFileName
Strick
-
Strick,
Variables that are specified as ReadWriteVariables under Custom Properties of the Script dialog can only be referenced in the PostExecute subroutine. If you want to access the values in the Input0_ProcessInputRow subroutine you need to specify them as ReadOnlyVariables in the dialog.
Hope this helps,
Bryan
-
Great thanks!
Strick
-
I must not be getting something - I understand that you can't access the variables unless you put them in as readonly - what do you do if you need a readwrite variable?
Thanks!
-
If you have a value that you need to store, create a class member and modify it during data flow execution. And then in your PostExecute method store the value.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi
To assign values to variables inside a script task you must "declare" them in the ReadWriteVariables section of the "Script Task Editor" on the script tab.
To access variables but not change their values inside a script task you must "declare" them in the ReadOnlyVariables section of the "Script Task Editor" on the script tab.
Regards
Christo
-
I'm having trouble with this as well and I believe I have everything set properly. I want to be able to pull area code (ac) and phone (p) from different points in different file depending on source. There must be a way to get a read-only variable available at the row level.
Any insight would be greatly appreciated! 🙂
The location property points to UNC path (i.e. \\server1\inputfiles\phones.txt)
When I exit this Script I still see a validation error saying:
Error2Validation error. Data Flow Task: Script Component [222]: Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)LoadPhoneVar.dtsx00
Script Component Property:
ReadOnlyVariables = acstart,pstart,location
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Private textReader As StreamReader
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim location As String
location = Me.Variables.location.ToString
textReader = New StreamReader(location)
End Sub
Public Overrides Sub CreateNewOutputRows()
Dim nextLine As String
Dim acstart As Int16
Dim pstart As Int16
acstart = CType(Me.Variables.acstart.ToString(), Int16)
pstart = CType(Me.Variables.pstart.ToString(), Int16)
nextLine = textReader.ReadLine
Do While nextLine IsNot Nothing
With StateDNCBuffer
.AddRow()
.phone = Mid(nextLine, acstart, 3) + Mid(nextLine, pstart, 7)
End With
nextLine = textReader.ReadLine
Loop
End Sub
End Class
-
Rather than listing your variables in the Read Only section of the component, you will almost certainly have more success by using VariableDispenser to read them.
I can post an example if you need one ...
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I gave that a shot when I looked this up yesterday... it seemed like overkill. But this was the script. Note that I've taken out the variable names from the ReadOnlyVariables property. There is nothing in the ReadWriteVariables either.
I still get an error... any suggestions?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Private textReader As StreamReader
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
Dim location As IDTSVariables90
Me.VariableDispenser.LockOneForRead("location", location)
textReader = New StreamReader(location(0).Value.ToString)
location.Unlock()
End Sub
Public Overrides Sub CreateNewOutputRows()
Dim nextLine As String
Dim acstart As IDTSVariables90
Dim pstart As IDTSVariables90
Me.VariableDispenser.LockOneForRead("acstart", acstart)
Me.VariableDispenser.LockOneForRead("pstart", pstart)
nextLine = textReader.ReadLine
Do While nextLine IsNot Nothing
With StateDNCBuffer
.AddRow()
.phone = Mid(nextLine, CType(acstart(0).Value, Int16), 3) _
+ Mid(nextLine, CType(pstart(0).Value, Int16), 7)
End With
nextLine = textReader.ReadLine
Loop
acstart.Unlock()
pstart.Unlock()
End Sub
Public Overrides Sub ReleaseConnections()
textReader.Close()
End Sub
End Class
-
There must be something simple that I'm missing.
If you can provide a working sample that would be appreciated.
-
Ahhhh... it's not variable at the row-level I have an issue with. It's the variables used for the file connection.
I finally got it worked out by positioning the file connecting on the PreExecute block:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Public textReader As StreamReader
Public Overrides Sub PreExecute()
Dim location As String
location = Me.Variables.CurrFile
textReader = New StreamReader(location)
MyBase.PreExecute()
End Sub
Public Overrides Sub CreateNewOutputRows()
Dim acstart As Int16
Dim pstart As Int16
Dim nextLine As String
acstart = Me.Variables.acstart
pstart = Me.Variables.pstart
nextLine = textReader.ReadLine
Do While nextLine IsNot Nothing
With testBuffer
.AddRow()
.area = Mid(nextLine, acstart, 3)
.phone = Mid(nextLine, pstart, 7)
End With
nextLine = textReader.ReadLine
Loop
End Sub
Public Overrides Sub ReleaseConnections()
textReader.Close()
End Sub
End Class
-
Well done for persisting with this one.
In case you're still interested, here's a function I sometimes use for reading variables:
Private Function GetVariable(ByVal varName As String) As String'Sub to set the value of a variable
Try
Dim vars As Variables
If Dts.VariableDispenser.Contains(varName) = True Then
Dts.VariableDispenser.LockOneForRead(varName, vars)
Try
GetVariable = vars(0).Value.ToString
Catch ex As Exception
' Write out an error, with the exception generating target site
' and the exception message
Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)
Dts.TaskResult = Dts.Results.Failure
Finally
vars.Unlock()
End Try
End If
Catch ex As Exception
Throw ex
End Try
End Function
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply