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 am I missing ?
Thanks in advance