Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failed to lock variable Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 9:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
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?


Post #547605
Posted Thursday, August 7, 2008 4:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 20, 2014 4:09 AM
Points: 1,865, Visits: 590
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
Post #548137
Posted Thursday, August 7, 2008 7:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
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


Post #548298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse