Accessing varibles inside the ScriptTask

Hi All,

In one of my ScriptTasks, I instantiate FileSystemWatcher class and set events for it. This has been done inside the Main() method. I can access all the varibles declared in the SSIS package inside the Main() method (by using Dts.Variables("").Value) but none can be accessed inside the event methods. One possible reason for this can be, events might be running in different threads.

Now my question is, How the varibles can be accessed inside the event methods.

Hope someone can give me a solution. Appricate all your solutions.
Thanks

Hi,

You might want to try using Daniel Read's method of accessing variables here:

http://www.developerdotstar.com/community/node/512

If that doesn't work, then it must be a thread-locking problem, although I can't see how that could really happen.

HTH|||

hi,

How odd, I've tried this snippet of code and it works fine:

Public Sub Main()

uno()

Dts.TaskResult = Dts.Results.Success

End Sub

Public Function uno()

MsgBox(Dts.Variables("RutaFicheroCarga").Value)

End Function

|||

Hi,

I tried the Daniel's method but it didn't work for me. The actual code looks like below.... to show you the exact problem.

Public Sub Main()
Dim watcher As New FileSystemWatcher()
......
......
AddHandler watcher.Created, AddressOf OnChanged
......
......
Dts.TaskResult = Dts.Results.Success
End Sub

Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
Try
MsgBox(Dts.Variables("MyVariable").Value.ToString()) ' this gives the error "Object reference not set to an instance of an object"
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub

Looking forward to seeeing a solution for this.

Thanks

|||

SL Coder wrote:

Hi,

I tried the Daniel's method but it didn't work for me. The actual code looks like below.... to show you the exact problem.

Public Sub Main()
Dim watcher As New FileSystemWatcher()
......
......
AddHandler watcher.Created, AddressOf OnChanged
......
......
Dts.TaskResult = Dts.Results.Success
End Sub

Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
Try
MsgBox(Dts.Variables("MyVariable").Value.ToString()) ' this gives the error "Object reference not set to an instance of an object"
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub

Looking forward to seeeing a solution for this.

Thanks

What is the data type of MyVariable? You may need to convert it to the Object data type.|||Have you tried changing the Private modifier on your sub to Public?

Also, you have to make sure that your variable is indeed instantiated. What is the type of your variable? If it is a reference type, it has to be somehow somewhere declared as New.|||

It is a string type variable and it can be accessed in Main() method without any problem. I made the event method as public but no luck. Also note that variable has been added to SSIS with scope of the "project". This problem not just for this variable, No variables can be accessed through the event method.

Any suggestions?

Thanks

|||Hmmm...

Dim watcher As New FileSystemWatcher()

Shouldn't it be:

Dim WithEvents watcher as New FileSystemWatcher()|||

Not sure why it does not work, but I have found writing custom tasks is often easier when trying anything other than simple stuff, if only because I can choose my language, and the IDE is much better. Not much use, but perhaps this would be -

File Watcher Task (http://www.sqlis.com/23.aspx)

|||

Hi Jon, Darren

Thanks for reply. I tried with WithEvents but it didn't work again. I have tried FileWatcherTask (sqlis) before but one biggest problem with it is, it stops listening once the first file is detected.

l'll try to make a custom task and see.

Thanks

Accessing Report Parameters Class through Web Service

I am using ASP .NET along to render my reports. I've extended the web
service class such that I specify the report folder, and I get all
reports and their respective parameters. I then dyncamically create
the controls (such as datepicker, textbox, etc...) based on the
Parameter Name, and Type. The drop down list is the one giving me the
hardest time though.
My problem is this: I have a Report with 3 parameters. The first 2
are dates. I can retrive all info on them just fine. The name,
prompt, and type (datetime). The third parameter (AppUserKey) however
is a dropdown list of all the users. I wrote a stored procedure that
does this called spGetAppUsers. Here is the structure of this
parameter as per the RDL file:
</ReportParameters>
...
...
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
I can get the Name, and even from the ValidValues, the ValueField and
LabelField, however, I want the <DataSetName> entry.
This is so that after I dynamically create the drop down list, I'm
able to populate it via the stored procedure. Recall that the
ReportParameter class is defined here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
ReportParameter::QueryParameter is a boolean property. Doesn't
actually return the name. Just whether or not you are using a query.
ReportParameter::DefaultValues is a string but is empty, since the
report hasn't executed the stored procedure (makes sense).
I want this to be a generic solution. Hence all this trouble to get
all the "givens" from the Report, and to reconstruct it in ASP .NET.
I need that DataSetName!!! :-S
Any help is appreciated.
Thanks for your time,
RoyThere are 2 ways to do what I want:
1. Instead of trying to get the stored procedure of my query based
parameter, I should've set forRendering = True in my
GetReportParameters function. That will actually return the query
based parameter set. Then I can populate the drop down list with that.
2. Load the .RDL (report definition in XML) in memory and use XPATH to
get the name of the parameter:
Dim reportDefinition As Byte() = Nothing
Dim doc As New System.Xml.XmlDocument
Try
reportDefinition = rs.GetReportDefinition(ReportsFolder &
strReportName)
Dim stream As New MemoryStream(reportDefinition)
doc.Load(stream)
In this scenario, assuming you have forRedering=True or False, you
still need to know the name of the parameter. So you'ld probably get
then name of the parameter and then check the value of QueryParameter
to see if it's true.
Then base your XPATH query on the following XML:
"/ReportParameter/ValidValues/DataSetReference/DataSetName[@.text]"
Or something similar. I forget. The above is proabably wrong and
could be written in a million different ways.
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
Roy Assaly wrote:
> I am using ASP .NET along to render my reports. I've extended the
web
> service class such that I specify the report folder, and I get all
> reports and their respective parameters. I then dyncamically create
> the controls (such as datepicker, textbox, etc...) based on the
> Parameter Name, and Type. The drop down list is the one giving me
the
> hardest time though.
> My problem is this: I have a Report with 3 parameters. The first 2
> are dates. I can retrive all info on them just fine. The name,
> prompt, and type (datetime). The third parameter (AppUserKey)
however
> is a dropdown list of all the users. I wrote a stored procedure that
> does this called spGetAppUsers. Here is the structure of this
> parameter as per the RDL file:
> </ReportParameters>
> ...
> ...
> <ReportParameter Name="AppUserKey">
> <DataType>Integer</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>AppUserKey</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> <LabelField>AppUserName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> I can get the Name, and even from the ValidValues, the ValueField and
> LabelField, however, I want the <DataSetName> entry.
> This is so that after I dynamically create the drop down list, I'm
> able to populate it via the stored procedure. Recall that the
> ReportParameter class is defined here:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
> ReportParameter::QueryParameter is a boolean property. Doesn't
> actually return the name. Just whether or not you are using a query.
> ReportParameter::DefaultValues is a string but is empty, since the
> report hasn't executed the stored procedure (makes sense).
> I want this to be a generic solution. Hence all this trouble to get
> all the "givens" from the Report, and to reconstruct it in ASP .NET.
> I need that DataSetName!!! :-S
> Any help is appreciated.
> Thanks for your time,
> Roy