How to read/write global package variables in an active x script?

Is this possible. How can one do it? Thanks for any info you have.

Are you using DTS or SSIS? This is an SSIS only forum, if you're using DTS.

Stay away from Active X scripts in SSIS as that is a deprecated technology.|||Using SSIS. I know I shouldn't, but I have some legacy DTS code I need to quickly move over. Is it possible to read variables from an active x script? Thanks.

How to read the names of package variables in custom task

Hi there,
I found a lot posts about package variables. But nothing about how getting access to the
names of the package variables programatically.
Why? - Well I want to create a list of variable names from which the user can choose
one of in a UI of a custom task. That's all... :)
I thought of using the variable dispenser in the Initialize method of the task and then storing the variable names into a property which can be read by the UI through the taskhost.
But it seems that you have to know all your variables by name if you want to use then in a task.
Any idea?
Fridtjof
In your IDtsTaskUI.Initialize method, capture the TaskHost. Store it in a field and pass it through to the form within GetView. To get a list of variables in the form, just use the Variables collection that is exposed on the TaskHost.
|||Thanks again Darren,
I got confused by the VarableService which is available from the IServiceProvider in the contructor of the UI form which does not lead to a solution.
I found some sample code in BOL to read out the variable names:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9mref/html/P_Microsoft_SqlServer_Dts_Runtime_Variables_Item_1_b4c5a2da.htm
CU
Fridtjof
|||

You should use the LockForRead when accessing them from the component class. However, on the UI it is easiest to "Variables vars" collection to access the SSIS Package Variables.

e.g. vars["Name_Of_Variable"].Value

Stuart

How to read the names of package variables in custom task

Hi there,
I found a lot posts about package variables. But nothing about how getting access to the
names of the package variables programatically.
Why? - Well I want to create a list of variable names from which the user can choose
one of in a UI of a custom task. That's all... :)
I thought of using the variable dispenser in the Initialize method of the task and then storing the variable names into a property which can be read by the UI through the taskhost.
But it seems that you have to know all your variables by name if you want to use then in a task.
Any idea?
FridtjofIn your IDtsTaskUI.Initialize method, capture the TaskHost. Store it in a field and pass it through to the form within GetView. To get a list of variables in the form, just use the Variables collection that is exposed on the TaskHost.
|||Thanks again Darren,
I got confused by the VarableService which is available from the IServiceProvider in the contructor of the UI form which does not lead to a solution.
I found some sample code in BOL to read out the variable names:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9mref/html/P_Microsoft_SqlServer_Dts_Runtime_Variables_Item_1_b4c5a2da.htm
CU
Fridtjof

How to read a set of rows into session variables? C#

Hello ASP.NET C# and SQL gurus

I want to read the results of a set of rows into session variables -- how is it possible?

Let me try explain. I have a query which returns multiple rows, e.g. the following query

SELECT PROFILE_ID, PROFILE_NAME FROM USER_PROFILES returns 5 rows i.e 5 sets of profile_ids and profile_names.

Now, I want to capture these and store them in session variables thus.

Session["PROFILEID_1"] =

Session["PROFILEID_2"] =

Session["PROFILEID_3"] =

Session["PROFILEID_4"] =

Session["PROFILEID_5"] =

Session["PROFILENAME_1"] =

Session["PROFILENAME_2"] =

Session["PROFILENAME_3"] =

Session["PROFILENAME_4"] =

Session["PROFILENAME_5"] =

Thanks in advance!

Fouwaaz

Hi,

Why do you store them in seperate rows. You can store it as datatable and retrieve it when ever you need.

for ex

your select query returns a DataTable dt

then assign it session Session["myTable"] = dt

then when ever you need cast that object to datatable like Session["myTable] as DataTable

Hope it helps

|||

just do a loop of the returned rows.. for example

using (IDataReader rdr = [your returned data read])
{
Session[rdr.GetOrdinal("Profile_ID")] = blah;
Session[rdr.GetOrdinal("Profile_Name")] = blah;

}

Personally, I won't suggest you put so many things in your session object. I'd simply store the entire returned Rows in whatever format when I need to use it.

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<