Page 1 of 1
Sql query with TI parametin Performance Modeler.
Posted: Fri Apr 24, 2015 4:38 pm
by gdimit
I am trying to create a import process with not much success.
In Architect in turobintegrator I was able to create a process in which the sql query received parameter and the process was executed.
For example select * from lib.table where name = '?p0? and 'when the process was executed the user was prompted for a parameter and
the sql query was executed with this parameter.
In Performance Modeler I tried the same technique with no success.
Is there a way to achieve the above in Performance Modeler?
Am I missing something?
Re: Sql query with TI parametin Performance Modeler.
Posted: Tue Apr 28, 2015 7:52 am
by TrevorGoss
Hello,
In Performance Modeler I tried the same technique with no success.
What, excatly, is the errror that you are getting from Performance Modeler?
It may be the case that Performance Modeler has changed the name of your variables, or created boiler plate code that conflicts with pre-existing code, without the actual error, it is hard to tell.
Thanks.
Trevor.
Re: Sql query with TI parametin Performance Modeler.
Posted: Tue Apr 28, 2015 6:26 pm
by gdimit
Hi Trevor,
I try the following sql
SELECT [Name]
,[Year]
FROM [Test].[dbo].[Table_1]
where [Name] > CAST('10' as int)
and the process execute.
Create a parameter pName with type Text
I change the above sql as
SELECT [Name]
,[Year]
FROM [ELBISCO].[dbo].[Table_1]
where [Name] > CAST('?pName?' as int)
Clicking on the Map or Advanced tab I get the following
SQL statement execution failed.: 22005[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the varchar value '?pName?' to data type int.
Thanks
George
Re: Sql query with TI parametin Performance Modeler.
Posted: Tue Apr 28, 2015 6:44 pm
by declanr
Having used PM much for these things but it has a prolog tab so just use your parameter/s to build up a string which is your SQL query and then use the datasourcequery function to set it there. Leave the actual data source tab to just be the full SQL query and it will be fine.
Re: Sql query with TI parametin Performance Modeler.
Posted: Tue Apr 28, 2015 7:23 pm
by gdimit
declanr wrote:Having used PM much for these things but it has a prolog tab so just use your parameter/s to build up a string which is your SQL query and then use the datasourcequery function to set it there. Leave the actual data source tab to just be the full SQL query and it will be fine.
Finally is what I do, but I was thinking if I was missing something.