October 6, 2011 at 10:24 am
I have an Exe SQL Task where I call a stored procedure which takes in a filename and returns true or false if the file is found. I'm trying to set this up so that it can have a parameter that is dynamic. I will be running the pkg with many different sets of data files where one may have a prefix of IPC_ and another set may have a files starting with THR_. I have a parent variable in a pkg called master that uses an exe package task to run the pkg with the exe sql task. Basically this is how I tried to set up the exe sql task:
exec MySprocName ? + 'TheRestOfTheFilename'
With the parameter Mapping set to a variable holding the value "IPC_". The variable is set to the value of a parent variable in the SSIS Parent Variable Configuaration. I'm getting the following error:
[Execute SQL Task] Error: Executing the query " exec uspDataFileExists ? + "inscode_t"" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is there any way to do this? Another thought is if there is a way to set a local variable equal to a parent variable and concatenate the rest to it but I couldn't find a way to do it.
October 6, 2011 at 10:49 am
I figured it out. I went into the Expressions for the Exe SQL task and set the SQLStatementSource to the following:
"Exec uspDatafileExists " + @PrefixVariableName + "inscode_t"
October 6, 2011 at 10:55 am
I figured it out. I went into the Expressions for the Exe SQL task and set the SQLStatementSource to the following:
"Exec uspDatafileExists " + @PrefixVariableName + "inscode_t"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply