R/W access problem with var in script task

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

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

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

Public Overloads Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Buffer Is Nothing Then
Throw New ArgumentNullException("buffer")
End If
Dim vars As IDTSVariables90
Dim varServerName As IDTSVariable90
Dim varPackageName As IDTSVariable90

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

Rahul, to be clear, when using the variable lock lists provide in the UI of the the Script Component you cannot access variables in the process input member (MyBuffer_ProcessInputRow). You can access read-only variables in the PreExecute and read-write variables in PostExecute

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

sql

R/W access problem with var in script Component

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

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

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

Public Overloads Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Buffer Is Nothing Then
Throw New ArgumentNullException("buffer")
End If
Dim vars As IDTSVariables90
Dim varServerName As IDTSVariable90
Dim varPackageName As IDTSVariable90

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

Rahul, to be clear, when using the variable lock lists provide in the UI of the the Script Component you cannot access variables in the process input member (MyBuffer_ProcessInputRow). You can access read-only variables in the PreExecute and read-write variables in PostExecute

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

Quoted Identifiers wrong while scripting

Hi all,
I am scripting out my database and it is setting Quoted Identifiers to ON in
the script just before my stored procs. I have Quoted Identifiers set to
off at the database level.
Is there some Stored Proc level script I have to run to have them turned off
so the script turns out correct?
Can't find anything in help.
?From the doc
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
ANSI_NULLS settings are captured and used for subsequent invocations of that
stored procedure.
Apparently it was created with the setting ON
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Grab the code from the proc, drop it, and re-create it manually adding SET
QUOTED_IDENTIFIER OFF to the beginning. Then when you script it, it should
be correct.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2#p#CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Thanks,
Unfortunately, my stored procs were created two years ago, I routinely
script them out for use in an MSDE database and they seemed to have changed
somehow to use that setting and so I have no clue how they were recreated
with a setting that was never on. I just tried to recreate then to no avail
either.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uBpibIDuDHA.2132@.TK2MSFTNGP10.phx.gbl...
> From the doc
> When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
> ANSI_NULLS settings are captured and used for subsequent invocations of
that
> stored procedure.
> Apparently it was created with the setting ON
> "A" <agarrettbNOSPAM@.hotmail.com> wrote in message
> news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > I am scripting out my database and it is setting Quoted Identifiers to
ON
> in
> > the script just before my stored procs. I have Quoted Identifiers set
to
> > off at the database level.
> >
> > Is there some Stored Proc level script I have to run to have them turned
> off
> > so the script turns out correct?
> >
> > Can't find anything in help.
> >
> > ?
> >
> >
>

Quick question on VB

Hey folks,

I am trying to run a batchfile or a external process ( exe ) from within a Script Task and read the process status returned by the process. Is there a sample VB script code that does this that you guys can share ? What modules do I have to Import ?

Thanks

-chiraj

This should do the trick. To test this, create a batch file in C:\temp called test.bat. Simply put "foobar" or something invalid in the batch file to create an errorlevel. Copy the code into a script task, run the package. It will show you the errorlevel in a message box.

Imports System

Imports System.Data

Imports System.Math

Imports System.Windows.Forms

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim stat As Integer = ExecProc("C:\\temp\\test.bat")

MessageBox.Show("The exit status was : " + stat.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

Public Function ExecProc(ByVal Path As String) As Integer

Dim objProc As System.Diagnostics.Process

Dim status As Integer

Try

objProc = New System.Diagnostics.Process()

objProc.StartInfo.FileName = Path

objProc.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal

objProc.Start()

'Wait until the process passes back an exit code

objProc.WaitForExit()

status = objProc.ExitCode

Catch Ex As Exception

MessageBox.Show(Ex.Message)

Finally

'Release the objProc resources

objProc.Close()

End Try

Return status

End Function

End Class

HTH,

Kirk Haselden
Author "SQL Server Integration Services"

|||Thank you, Kirk.

Queued Transaction Failing

Let me put this in english (very long day).
The my myfile_0.sql is my pre-snapshot script that drops
and recreates the tables.

>--Original Message--
>Hello,
>Can anyone help me with a Queued Transaction thats
failing?
>I just set this up to do a snapshot and queued
>tranactional rep. The snapshot works great the queued
part
>brings the error (last command)
>\\IMYSERVER\Replication\unc\MyDirectory\200405271 60853
>\myfile_0.sql.
>I've checked out myfile_0.sql in QA and it works, and the
>snapshot deletes and recreates the tables (thats the
>script it running).
>Thanks for looking
>Rose
>.
>
Rose,
sorry but I'm still not too clear on what you want. Is the script actually
being propagated correctly but you don't want to drop the table at the
destination? In this case the behaviour that you have is controlled by the
@.pre_creation_cmd in sp_addarticle. In the GUI this is available on the
elipsis button next to the article (table). The default option is to drop
the table, but you also have the choice to leave it unchanged, truncate it
or remove selected rows.
HTH,
Paul Ibison
|||Firstly my apologies,
Looking at it again my remark 'let me put this in
english', was not nameed at you but me, occasionally I
have a habit of puting things in without proper proof
reading.
The Reason I delete the tables is thats what was
recommended by a white paper for transactional
replication, so I thought I would try it here.
Anyway I think I might of gotten to the bottom of it. The
database was a Transaction Replication database -
Immediate before this, and what I think is happening is
that it still thinks it is, so its not allowing me to
delete.
Anyway thanks Paul, and why aren't you a MVP ?
Rose

>--Original Message--
>Rose,
>sorry but I'm still not too clear on what you want. Is
the script actually
>being propagated correctly but you don't want to drop the
table at the
>destination? In this case the behaviour that you have is
controlled by the
>@.pre_creation_cmd in sp_addarticle. In the GUI this is
available on the
>elipsis button next to the article (table). The default
option is to drop
>the table, but you also have the choice to leave it
unchanged, truncate it
>or remove selected rows.
>HTH,
>Paul Ibison
>
>.
>
|||Rose,
you can use sp_removedbreplication on the subscriber before subscribing to
remove any traces of replication, or sp_MSunmarkreplinfo on the offending
table.
Thanks for your comment - MVP status would be extremely welcome but anyway
the way I look at it is that as I train the MS course on replication
(www.pygmalion.com) answering questions is still a good way of keeping on
top of things.
Cheers,
Paul

Questions on WHILE LOOP in PL/SQL

I have developed the piece of script but the look up part wont work properly so i wonder is there a way of doing a lookup all the way from the bottom level to the top level. Thanks in advance.


SET SERVEROUTPUT ON
SET VERIFY OFF
-----------

ACCEPT sailorID NUMBER PROMPT 'Enter sailor id: '

DECLARE
L number;
X number;
p_sid sailors.sid%TYPE;
p_trainee sailors.trainee%TYPE;

BEGIN
SELECT sid, trainee
INTO p_sid, p_trainee
FROM sailors
WHERE sid = &sailorID;

L := 0;
X :=&SailorID;

WHILE p_sid = X and p_trainee != 99 LOOP

X := p_trainee;
DBMS_OUTPUT.PUT_LINE('+++++ SailorID '||p_sid||' Train '||p_trainee);
DBMS_OUTPUT.PUT_LINE('loop was executed');

L := L+1;
p_trainee := 97;
p_trainee := p_trainee + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('LEVEL: ' || L);
---------------
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('++++ Error !!!! '||&sailorID||' is not a
valid ID');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('+++++ '||SQLCODE||' ... '||SQLERRM);
END;
.
RUNHello,

one way to develop a lookup is to declare a cursor. This is the standard
way to declare a cursor:

ACCEPT sailorID NUMBER PROMPT 'Enter sailor id: '

DECLARE
CURSOR cuLookup IS
SELECT sid, trainee
INTO p_sid, p_trainee
FROM sailors
WHERE sid = &sailorID;

L number;
X number;
p_sid sailors.sid%TYPE;
p_trainee sailors.trainee%TYPE;
rLookup cuLookup%ROWTYPE;

BEGIN

L := 0;
X :=&SailorID;

OPEN cuLookup;
FETCH cuLookup INTO rLookup;

WHILE cuLookup%FOUND LOOP
END LOOP;

CLOSE(cuLookup);

EXCEPTION
WHEN OTHERS THEN
IF cuLookup%ISOPEN THEN
CLOSE cuLookup;
END IF;
END;

There ar several other ways to do a cursor fetch f.e. you can use a direct declarion in a for statement. Look into the oracle manual to get further details.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com