September 29, 2006 at 10:53 am
AARGH! I am at my wit's end. I have searched documentation, Online SQL, this forum, MS, Google, ETC. to no avail.
Problem: I am FTPing flat files from a remote server to my local server. I want to check each file by file name and see if it has already been imported into my server, if it has not, then import it.
I have my FTP Task bring the files in.
I have my ForEach Loop scan through the files and put the full file name in a package parameter called FlatFileName.
In my ForEach Loop I have an Execute SQL Task with the following configuration:
Connection Type = OLE DB
SQLSourceType = Direct Input
SQLStatement = SELECT COUNT(*) FROM SLSMining.dbo.FlatFileDetectionList WHERE Category='USPS FTP' AND FlatFileName = ?
Parameter Mapping = Variable Name User::FlatFileName, Direction Input, Data Type VARCHAR, Parameter Name ?
When I run the script, it returns with the following error: "Parameter name is unrecognized."
What, pray tell, am I doing wrong?
Michael Lee
September 29, 2006 at 12:27 pm
I am not sure if I understood correctly this:
Parameter Mapping = Variable Name User::FlatFileName, Direction Input, Data Type VARCHAR, Parameter Name ?
but the "Parameter name" in your case should be 0 (not ?), because your connection is OLE DB and you have one parameter (The names of parameters in OLE DB are numbers starting from 0, i.e. 0, 1, 2, ..., in the order they appear in the query)
September 29, 2006 at 12:46 pm
Thank you! That was the trick.
Michael Lee
September 29, 2006 at 2:26 pm
Glad to be helpful.
On the other side, there should be a better way to access user variables in queries. What if I want to use the same variable more than once? What if I have several variables and need them several times in "random" order? I guess this method implies to define a new parameter even this is the same varaible but appears again few spots later. Like in:
SELECT apples FROM fruits WHERE size>? AND size<2*?
and ? refers to USER::CUTOFF. Then you need
variable USER::CUTOFF ... parameter name 0
variable USER::CUTOFF ... parameter name 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply