SSIS - How to create a parameter for parameterized complex multi-statement script in OLE DB Source and join by this parameter with another source

Question

I'm stuck with the following SSIS issue. I need to create a complex parameterized script for OLE DB Source in Data Flow component (SQL Server 2008). In addition to that, I need to join it with another OLE DB source by the script parameter. The purpose of this exercise is to set a value in output column based on conditions specified in the script. The script should look like this (this is simplified version, it's much more complex involving multiple IF statements & complex joins):

declare @ind int
set @ind=0
if (select status from contracts where contract_id=?)=1
  then @ind=1
if (select year(completion_date) from orders where contract_id=?)>2012
  then @ind=1
select @ind

The questions:

1. It doesn't allow me to map parameter if script contains anything besides simple SELECT (IF, declare, etc.), getting "Parameters can't be extracted from SQL Command" error. Is it the way it's supposed to work? Then, I guess, I may need to move it to Stored Procedure...
2. Even with the Stored Procedure, I'm still facing the major obstacle here - I need to map this OLE DB source component with another source by contract_id field, which is parameter in this query. How can I accomplish it?

Answer 1

Leo,

A little background first. When using a sql query with parameters in the ole db source component, two things need to happen. One is, the data type of the (undeclared) parameters need to be decided and the second one is the metadata of the query output need to be decided.

I am saying that the parameters are undeclared because, in the sql command that goes to SQL Server, there are no declare statements that define the datatypes of these parameters. So from SQL Server's perspective, it does not know the data type and thus these parameters are undeclared. Compare this to how we would write the same query in SSMS; where we first declare the variables (for example: declare @my_var int; select val from table where col = @my_var; here, we declared our @my_var variable upfront.).

So since the parameters are "undeclared", when running the query against SQL Server, it first needs to determine the data types of these parameters. And when it comes to this, SQL Server 2012 onwards has better functionality than the previous versions.

In SQL Server 2012, a new proc called sys.sp_describe_undeclared_parameters was introduced. This procedure helps infer the datatypes of parameters even for complex tsql statements. It may not always give the datatypes you are expecting, but you can give enough hints to get the exact data types you need. However, this is only available from SQL Server 2012 and onwards.

You said you are using SQL Server 2008. In 2008, the driver (Native Client 10.x) creates a special select statement (with columns against which the parameters are assigned to) and uses fmtonly to infer the data types for undeclared parameters. And in my testing, this only works in simpler cases and fails in more complex tsql statements. And thus you get the error message saying that the "provider is not capable...". However, if you encapsulate the logic in a stored procedure and have parameters to those stored procedures, SQL Server can easily find the data type for these "undeclared" parameters. Becuase: when we create the stored procedure, we declare the datatypes for the parameters, the 2008 driver uses the sys.sp_procedure_params_100_rowset proc to get the data types.

So using a stored procedure with predefined parameters might be the way for you to go here (because you are on 2008). However, if you move to 2012 or later, you can rely on the newer functionality.

As for the second half of getting the query output metadata, having temp tables will pose a challenge. If you are on SQL Server 2008, you can use some creative use of fmtonly to workaround the presence of temp tables in your stored proc (search online or go here ). And if you are on SQL Server 2012 and onwards, you can make use of WITH RESULT SETS clause to work around temp tables.

Update (based on Leo's first Comment below):

The short answer is yes. In 2008 world, complex tsl statements were not being able to be parsed to infer the parameters and their data types. Looks like only simple select statements or "exec proc" statements are the right candidates. With SELECT statements, JOINs are ok; operators such as UNION/INTERSECT/EXCEPT etc are ok (as long as the each individual statement is a select statement). Subqueries were not ok. Even comment tags preceding an otherwise simple select/exec throw up the error.

This is what I observed happening behind the scenes with 2008 driver. Let us say the following is our select statement with params:

select A.col_a, A.col_b from dbo.MyTable A where A.col_c = ? and A.col_d = ?

Then, when inferring parameter types, either the driver or the BIDS (not sure which) constructs and sends the following sql commands to the database:

set fmtonly on;
select col_c, col_d from dbo.MyTable where 1 = 2;
set fmtonly off;

Notice, the construction of this sql statement. It contains the two columns (col_c and col_d) to which the params are being assigned to. Also, there is a where clause to prevent any actual rows from returning. After construction, this statement is basically sent to sql server, so that it can know what the data types of those two columns are; and thus assign those data types to our undeclared parameters.

So the process (driver or the BIDS) trying to infer these data types is only capable of constructing these sql statements in simpler scenarios and it is unable to do so in complex scenarios; at which point it throws the error

However, if you were to use procs with 2008, no construction of special sql statements is done and instead, the sys.sp_procedure_params_100_rowset proc is used to infer the data types.

Finally, if you were to use 2012 (or onwards) though, the newly added proc sys.sp_describe_undeclared_parameters is lot more capable and can handle some complex scenarios too. Basically, here, the sql statement (whatever it is) from your oledb src component is sent as is as an input to the aforementioned proc; and the proc returns a dataset describing the parameter data types. So, for our example, in 2012, this would happen:

exec sys.sp_describe_undeclared_parameters N'select A.col_a, A.col_b from dbo.MyTable A where A.col_c = ? and A.col_d = ?'