Failed to lock variable
-
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?
😀
-
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
-
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
😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply