Accessing a package's variables from within a custom log provider...or not...

Hi,

Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx

...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?

We live in hope,

Tamim.

I have recently looked at this and would agree, there is no way to use variables in a log provider. There are no hooks available in any of the base class methods or any parameters that you are exposed to in a log provider. Would be nice if you could though, as it would give you more control to create your own message content, but as it stands this is not viable.|||

Thanks Darren - I was pretty sure there was no alternative, so it's good to have that ratified by yourself. There is however one cheeky/not-so-neat way round: the 'source' for some events, e.g. PackageStart, gives the package name. (Of course this is an exception for only 1 system variable, with no wider applicability).

Cheers,

Tamim.

Access to variables in Custom Log Provider?

Hello,

I found one post from the past asking this question, but didn't see an answer, so I am hoping someone can shed some more light on it:

I am writing a custom log provider in C#. Is it possible give this custom log provider access to the variables collection (via a variabledispenser object)? First glance at the log provider classes would tell me that this is not possible, though I would like confirmation. Also, if I am correct in thinking it is not possible, what was the reason for leaving this out?

Thanks!

Indeed this does not appear to be possible. What is it you are trying to acheive? Some more info may help us evaluate this as a feature request for the future.

Donald

|||

Donald,

Thanks for the response. A brief explanation of my thought process below:

I have a package with a number of variables that are evaluated during package execution via property expressions. When a component of the package logs a warning or failure, I would like to be able to identify what state each variable was in at the time of that warning/failure. I had hoped to be able to create a custom log provider to log this information to a database along with the warning/failure message. If the VariableDispenser object was passed into the Log method of my custom log provider, I would be able to accomplish this.

Thanks again.

David

Access To Dts.Variables Causes Exception

Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks.

I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile

Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following:

Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User:trDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString()

Everytime this script executes I get the following errors:

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25

I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that.

Expresion property set as false also.

Any ideas? Thanks.

Right Click the script task, press F4, this will show the properties of the script task, there you will have properties called ReadOnlyVariables, ReadWriteVariables. You can set the variables name in this based on your requirement.
These variables will be available in the script, else they dont have visibility inside the script task and an error will be thrown at run time.

Thanks

Access read only variables in Script Component in a Dataflow

I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.Use "Me.Variables.variableName"|||I was I am trying to do the same thing you mentioned but I am the following error

[Script Component [1463]] Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables() at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.Variables.get_LogPath() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ComponentWrapper:line 72 at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.ScriptMain..ctor() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ScriptMain:line 78 End of inner exception stack trace at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()|||Script Component or Script Task?|||Script Component
|||

Rohit Ghule wrote:

Script Component

Does your script component have all of the Imports?

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 Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in via ReadOnlyVariables

Row.MaxKey = MaximumKey 'Assign the output field of "MaxKey" to the value of the passed in variable
End Sub

End Class|||Searching this forum turned up more information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201158&SiteID=1

I think the key is to look at your variables in the Public Overrides Sub section.|||

Most methods in a script task are overrides of base class methods, so you need a bit more info. The PreExecute, which is public, and overridden, so starts with "Public Overrides PreExecute(.." supports the variable manager stuff, but may not always be what you want. If you really need to access variable at a row level then you can, see this thread-

Re: R/W access problem with var in script Component - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1)

Access parameters within dynamic SQL

I am exploring the use of dynamic SQL within a stored procedure and have run
in to a problem. The dynamic SQL has no visibility of variables declared
outside the dynamic SQL. Try this snippet which causes an error:
declare @.branch int
set @.branch = 10
exec ( 'select @.branch_no' )
Is there any way to make these variables visible to the dynamic sql without
concatenation?
The reason why this will be a problem is that I will be use the openxml
command within the dynamic sql. I will be using very large XML strings so I
am pretty sure that I will have problems concatenating XML strings with
dynamic sql statements.
Any ideas?
McGy
[url]http://mcgy.blogspot.com[/url]> The reason why this will be a problem is that I will be use the openxml
> command within the dynamic sql. I will be using very large XML strings so
> I
> am pretty sure that I will have problems concatenating XML strings with
> dynamic sql statements.
As long as each string is <= 8000 characters (or 4000 characters with
Unicode), you can say EXEC(@.sql1 + @.sql2 + @.sql3);
A|||Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
example which explains how to pass & return values from such strings.
--
Anith|||Great that was really useful. I have combined a couple of examples (openxml
and sp_executesql) from books online in the snippet below to show how XML
can be passed in as a parameter to dynamic sql:
DECLARE @.SQLString NVARCHAR(500)
/* Build the SQL string */
SET @.SQLString =
N'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, ''/ROOT/Customer'',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @.idoc'
/* Execute the string */
EXECUTE sp_executesql @.SQLString, N'@.doc text',
@.doc = '<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
McGy
[url]http://mcgy.blogspot.com[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#d272ckPGHA.740@.TK2MSFTNGP12.phx.gbl...
> Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
> example which explains how to pass & return values from such strings.
> --
> Anith
>|||>> am exploring the use of dynamic SQL within a stored procedure and have ru
n
in to a problem. <<
As well you should!! This is an awful way to even think of writing
code of any kind. Remember coupling, cohesion and all that stuff in
your fist Software Engineering course?
So you want on-the-fly, mixed, proprietary languages so you can
manipulate XML with T-SQL? This whole thing sounds like a pile of
kludges, but without better specs we can only guess at a relatioanl
solution.|||The reason for dynamic SQL is that I need to parameterize the database name
in the queries. We have a database with 20 odd tables with exactly the same
structure - so rather than duplicating the stored procedure 20 odd times I
am looking at writing it once with dynamic SQL.
The reason for XML is so that I can send large batches of data at a time to
the stored procedure. Which is very efficient.
I am not sure that I will use this technique but it is certainly one of
several I am considering. Its not a position I relish being in but that's
the way the database is so more likely than not I will have to work with its
shortcomings.
See another post by my titled "Parameterize table name without constructing
dynamic query?"
McGy
[url]http://mcgy.blogspot.com[/url]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141350534.964106.45490@.t39g2000cwt.googlegroups.com...
> in to a problem. <<
> As well you should!! This is an awful way to even think of writing
> code of any kind. Remember coupling, cohesion and all that stuff in
> your fist Software Engineering course?
>
> So you want on-the-fly, mixed, proprietary languages so you can
> manipulate XML with T-SQL? This whole thing sounds like a pile of
> kludges, but without better specs we can only guess at a relatioanl
> solution.
>

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

Access Global Variables in Body

Is this possible? I am trying to access the global variables via custom code.
However I get this msg when I use the following code.
Public Shared Function Hello() As String
Return Globals.UserID
End Function
ERROR :- There is an error on line 1 of custom code: [BC30469] Reference to
a non-shared member requires an object reference.
I need to get the global variables Execution time, Page Number within the
body to format the report appropriatly.
Thanks in advance to helpers !In order to access the Global (or parameter for that matter) you have to
fully qualify it in the code window.
There are two fixes to your problem.
1. drag a text box to the report area
2. past something like this into it:
=Globals!PageNumber & " of " & Globals!TotalPages
3. run the report.
--
OR (doing it by ref from the code)
--
if you need to ref them from the code, the you have to declare them first in
your code like so:
Function pn()
Dim pn as String
pn = Report.Globals!PageNumber
Return pn
End Function
Function tp()
Dim tp as String
tp = Report.Globals!TotalPages
Return tp
End Function
----
Once you have that in the code window, you can type:
=Code.pn()
or
=Code.tp()
--
to get the pagenumber or total page global respectfully
"d pak" wrote:
> Is this possible? I am trying to access the global variables via custom code.
> However I get this msg when I use the following code.
> Public Shared Function Hello() As String
> Return Globals.UserID
> End Function
> ERROR :- There is an error on line 1 of custom code: [BC30469] Reference to
> a non-shared member requires an object reference.
> I need to get the global variables Execution time, Page Number within the
> body to format the report appropriatly.
> Thanks in advance to helpers !