May 13, 2009 at 1:01 pm
In Execute SQL Task Editor, I have this SQL statement:
select LastName, FirstName from tblEmployee where EmployeeID = 1234
I like to substitue 1234 with a variable, which I created one called varEmployeeID
In Execute SQL Task Editor, there is a Parameter Mapping option which allows me to set up a parameter. I entered them as follows:
Variable name: User:: varEmployeeID
Direction: Input
DataType: Long
Parameter Name: ??? not sure what to enter here.
Paremeter size: -1 (default) ??? Not sure what to enter here
Please advise what to enter in the above and what to do with the Select statement to use the parameter.
od
May 13, 2009 at 3:11 pm
and change the query to:
select LastName, FirstName from tblEmployee where EmployeeID = ?
bc
[font="Arial Narrow"]bc[/font]
May 13, 2009 at 3:16 pm
thank for the reply.
Is there some special attention needed for setting up parameter if the Select statement involves an Union. For example,
select LastName, First name from tblemployer where EmployeeID = ?
union
select LastName, First name from tblemployer
inner join tbloffice on tblemployer.officeID = tbloffice.officeID
where tbloffice.officeID = ?
In my parameter mapping,
the first parameter name = 0
the second parameter name = 1
Each has its own user variable.
When I execute the query, it gives me an error.
"Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.".
May 13, 2009 at 3:25 pm
i've had this same pain myself....
read this:
basically, try setting "ByPassPrepare" to true and see if that helps.
another way is to build up the entire sql command as a variable and use that instead.
bc
[font="Arial Narrow"]bc[/font]
May 13, 2009 at 4:39 pm
The bypass thng doesn't work. You mention about building a variable for the sql statement, do you mean doing somethig like this?
Declare @sqlcmd varchar (max)
select @sqlcmd ='select xxxxxxxx'
exec (@sqlcmd)
If so, what do I do with the '?' in the select statement? It won't work if I use '?'.
May 13, 2009 at 4:48 pm
create an ssis variable like, varSQLCommand string
then use an expression for varSQLCommand:
"select LastName, First name from tblemployer where EmployeeID = " + varEmployeeID +
"union
select LastName, First name from tblemployer
inner join tbloffice on tblemployer.officeID = tbloffice.officeID
where tbloffice.officeID = " +varOfficeID
in your execute sql task use the sql command from variable.
hope that helps. gotta go, i'll check back in the am cst.
bc
[font="Arial Narrow"]bc[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply