SSIS - Passing User Variables to script task creates error

[tshad]

In 2008, I have a package that works fine where I am getting rows from my
sql query and then I pass that to a Derived Column Transformation which goes
to a Script Task that just write a piece of text and that goes to a Flat
File Destination.

In the Script task, I have the following:

public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named
"<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named
"MyOutput".
*/
Output0Buffer.AddRow();
Output0Buffer.Resultstext = "This is a test: "; //
+Convert.ToString(Variables.RowCnt);
}

I also have a User Variable RowCnt that gives me the number of rows sent.

This works fine. I comment out the Convert statement because at this point
you cannot see the variable since I don't have it set up as a
ReadOnlyVariable.

The file gets created with:

Column 0
This is a test:

But I also want to pass RowCnt so I can display it so I select User::RowCnt
from the list of ReadOnlyVariables. Now in the Script I can see RowCnt in
the Variables list. But I don't change the code at all and I now get an
error.

The Console shows up on my screen (but I can't stop it).
My file is created with nothing in it.
And another file is created - SQLDUMPER_ERRORLOG.log - with the following in
it:

(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input
parameters: 4 supplied
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter
1: 2808
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter
2: 0
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter
3: 0:0
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter
4: 0100D608
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parsed
parameters:
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ProcessID = 2808
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ThreadId = 0
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags =
0x0
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
MiniDumpFlags = 0x0
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
SqlInfoPtr = 0x0100D608
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir
= <NULL>
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ExceptionRecordPtr = 0x00000000
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ContextPtr = 0x00000000
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ExtraFile = <NULL>
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
InstanceName = <NULL>
(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ServiceName = <NULL>
(650:16F4) 01/24/11 12:39:47, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, Create dump
file C:\Program Files\Microsoft SQL
Server\100\Shared\ErrorDumps\SQLDmpr0001.mdmp failed 0x3 - The system
cannot find the path specified.

(650:16F4) 01/24/11 12:39:47, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Watson
Invoke: No

This apparently was caused by there being no "ErrorDumps" folder in it. It
shows up in the folder I had defined for my file.

When I add the ErrorDumps folder, it creates an SQLDmpr0001.mdmp and a
ISD7DE.tmp in it.

Why does passing this variable cause a problem???? I am not accessing it.
I originally thought my accessing it with my convert statement caused the
problem.

Thanks,

Tom

[Bob Barrows]

I'm not sure I'm following your description correctly. Perhaps this will
help ... ?
http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

[tshad]

Not really.

In my example, the script works as long as I don't include a
ReadOnlyVariable in the Scripts Property section. If I add one in then I
get the error without changing anything else.

And it is not like I misspelled or something because I am picking it out of
a list.

The console may have a message that means something but it closes right
away.

Also, the file is created to it obviously got past the script to the Flat
File Destination since that file is created and that is where it is
referenced.

Thanks,

Tom

"Bob Barrows" <reb0...@NOyahooSPAM.com> wrote in message
news:ihkpb0$fuc$1...@news.eternal-september.org...

[tshad]

I found that this error happens only when I put a variable in the
ReadOnlyVariable property. If I put the variable into the ReadWrite
Variable I don't get that error. Not sure why this is as I am not writing
to the variable.

I did find that I can't use the variable in my script actually. When it
runs and gets to the point where I accessed the variable I get the error:

The collection of variables locked for read and write access is not
available outside of PostExecute.

Actually, I seem to be able to use it in PreExecute as well but not in the
CreateNewOutputRows method.

I am setting a variable in the PostExecute and PreExecute methods but it
doesn't seem to do anything and leaves the original value that I initially
set it to.

My code looks like:

*********************************
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string myIntegerVariableValue = "201123"; //Set it here

public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
myIntegerVariableValue = Convert.ToString(Variables.RowCnt); //Set
here
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
myIntegerVariableValue = Convert.ToString(Variables.RowCnt); //Set
it here
}
public override void CreateNewOutputRows()
{
Output0Buffer.AddRow();
myIntegerVariableValue = Convert.ToString(Variables.RowCnt); //
Causes error here

// if left out it works fine but returns
201123
Output0Buffer.Resultstext = "This is a test with row count = " +
myIntegerVariableValue;
}
}
***********************************

So how do I get the value from this variable????

Thanks,

Tom
"tshad" <t...@dslextreme.com> wrote in message
news:MCm%o.1307$zw7...@newsfe12.iad...

[tshad]

I find that I can access the ReadOnlyVariable fine in a Script Task in the
Control Flow but cannot access ReadOnlyVariables from a Script Component in
the Data Flow.

I also cannot put breakpoints in the Script Component. But it works fine in
the script task.

Tom

"tshad" <t...@dslextreme.com> wrote in message

news:K0p%o.1492$gx6...@newsfe22.iad...