Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failed to lock variable


Failed to lock variable

Author
Message
Tim Parker
Tim Parker
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 460
Hi all,

I'm currently trying to complete a project that will need to feed a dynamically created SQL statement into a BCP command line variable that will ultimately be passed into an Execute Process Task where the variable will be applied to the task for the BCP.exe to run from. My question is this....

1.) Where should I plug in this BCP command line variable? Here is a sample of what the variable contains for the BCP.exe.

"SELECT * FROM Table WHERE CLUASE queryout C:\Folder\Filename.csv -c -t -r -SSERVERNAME-T"

When I execute the package I get an error message that say's that it failed to lock variable.

Here is my script task that creates the dynamic BCP command.

Public Sub Main()

Dim myArgument As String

Dts.Variables("varSQL").Value = "SELECT * FROM " + Dts.Variables("varCatName").Value.ToString() _
+ " WHERE " + Dts.Variables("varGroupFilter").Value.ToString() + " AND " + Dts.Variables("varFilter").Value.ToString()

Dts.Variables("varArgument").Value = Dts.Variables("varSQL").Value.ToString() + " queryout C:\CSVFiles\" _
+ Dts.Variables("varDBName").Value.ToString() + "_Segment_" + Dts.Variables("varActual_Segment_ID").Value.ToString() _
+ ".csv -c -t -r -S" + Dts.Variables("varServerName").Value.ToString() + "-T"

myArgument = Dts.Variables("varArgument").Value.ToString()

MsgBox(myArgument)


Dts.TaskResult = Dts.Results.Success
End Sub

Any ideas on what is causing the variable to fail to be locked and also where should I place this variable in the Execute Process Task for the BCP.exe?

:-D
hodgy
hodgy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1869 Visits: 596
is your varArgument variable in the readwrite variables collection?

an alternative to this is to use the dts.variablesdispenser to lock the variable for writing.

tom

Life: it twists and turns like a twisty turny thing

Tim Parker
Tim Parker
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 460
To anyone who is reading this post, I solved my issue with the variable.

First thing was that I was not using the Execute Process Task correctly. Since I am using a For Each Loop container to enumerate through the needed rows to feed my SQL statement. I ended up creating a batch file dynamically as the loop enumerated through the rows. Once the batch file was created I would then call the Excecute Process task and this would execute the BCP utility through the batch file.

Here is the code that I used...

Public Class ScriptMain
'I added the System.IO class to this package to allow for the creation of the Batch file below.

Public Sub Main()
'Dynamically build the SQL statement based on the enumerated row from the "For Each Loop". This statement is loaded into
'the "varSQL" package variable.
Dts.Variables("varSQL").Value = "bcp ""SELECT * FROM " + Dts.Variables("varDBName").Value.ToString() + ".." + Dts.Variables("varCatName").Value.ToString() _
+ " WHERE " + Dts.Variables("varGroupFilter").Value.ToString() + " AND " + Dts.Variables("varFilter").Value.ToString() + """"

'Dynamically build the BCP argument string based on the "varSQL" package variable. This is loaded into the
'"varArgument variable which will be passed to the Batch file below.
Dts.Variables("varArgument").Value = Dts.Variables("varSQL").Value.ToString() + " queryout ""\\ServerName\Folder\" _
+ Dts.Variables("varDBName").Value.ToString() + "_Segment_" + Dts.Variables("varActual_Segment_ID").Value.ToString() _
+ ".csv"" -c -t -r -S" + Dts.Variables("varServerName").Value.ToString() + " -T"

'Create the Batch file.
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
oWrite = oFile.CreateText("C:\Folder\mybat.bat")

'Write the BCP command string with the "varArgument" variable. Close the Batch file once complete.
oWrite.WriteLine(Dts.Variables("varArgument").Value.ToString)
oWrite.Close()


Dts.TaskResult = Dts.Results.Success
End Sub

:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search