Hi there,
I have a number of bcp files that I need to process into a database table that have similar names.
If I do a single file hard coded, my syntax works
But if I use a variable it errors and does not show an error except for the process code 1.
I start with an Execute SQL Task that truncates my table.
Then a fore loop container that points to the location of my bcp files and puts the name of the file into a variable. This part is working.
Within the Fore Loop Container I have an Execute Process Task that starts bcp and imports using an xml schema.
I have this in the executable line C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe
and I have this in the Arguments Line which is setup under the expressions window / arguments / @[User::varBCPData]
The variable @[User::varBCPData] has this in it and I can evaluate the expression and see exactly what I put in manually. The BCP_Filename.bcp gets populated from the fore loop.
"DatabaseName." + @[User::sqlStagingTable] + " IN " + @[User::varBCPFileDIR] + "BCP_Filename.bcp -S " + @[User::sqlDatabaseServer] + " -f " + @[User::varXMLFileDIR] + "" + @[User::varXMLFilename] + " -T"
When I hard code the syntax, this works.
Databasename.schema.TableName IN "\Server\Fileshare\RenamedBCPs\BCP_Filename.bcp" -S DabaseServer\InstanceName -T -f \Server\Fileshare\RenamedBCPs\XML_Filename.XML
So my question is if the expression looks exactly like what I put in manually, why does it not work ?
I need to iterate through all the bcp files in the directory and import them into the table an not have to do them singly.
What I'd suggest doing is print out the final command string and see if it's actually correct.by executing the printed command manually. Another possibility is a matter of privs. You may have the privs to get to the files, The system might not.
Thanks for your response Jeff.
Yes, I had done that already and it is the same.
What I think is the problem is the syntax when I use the variables.
I have got it working to the point where it starts up bcp but does not run the bcp statement.
I guess I will have to play with it a bit to get the syntax right. for example the \ for the beginning of my unc path and between directory names needs to be double......\\\\servername\\folder\\folder\\folder. I am not sure what else I will need.
not sure you understood Jeff's point? You need to output the exact command that will be issued so that you can then try that manually to see if you get an error - i.e. that is the exact command generated WHEN using the variables.
My money is on no "" between @[User::varXMLFileDIR] and @[User::varXMLFilename], because the FileDIR will likely no include a trailing slash.
But there is other stuff that can go wrong here - for example the "-T" parameter is in a different place in the string concatenation and your example command, whilst not significant it could lead to rogue spaces not separating individual parameters, or breaking parameters and their values (not that that usually matters ... but)
I do not think I have missed the point. That is exactly what I have done.
The output looks the same.
Thanks for your advice. The @User[::varXMLFileDIR ] does include the trailing slash.
I am running the bcp parameters where the vendor has put them. I can change that. You do not state where it should be though ? I have seen it on the end so will try there.
Thanks
Angie
I have this working.
Once I sorted the double slashes out I found that my fore loop needed a tweak as I was after a particular set of files in a directory.
So once I changed the files to look for *_partfilename.bcp it worked.
No you are fine, the sequence of parameters is not important - provided that they have spaces to separate them and so on. My only observation was that the example you posted, and the code that generates it, would have the "-T" in different places, so looked like what you were testing was not VERBATIM what the code generated. But you've clarified that you were testing the exact output from your code so that's fine.
That's good to hear It can be very tedious finding a bug like that. I revert to outputting a detailed trace of exactly what is going on as pretty much my first step, and then manually trying each command by hand, as that usually gives me the Eureka! moment pretty quickly when I spot the error that I had made ...
It can be very tedious finding a bug like that. I revert to outputting a detailed trace of exactly what is going on as pretty much my first step, and then manually trying each command by hand, as that usually gives me the Eureka! moment pretty quickly when I spot the error that I had made ...