CODEWITHSUNDEEP
azuressisazure-synapse
Neil P
Neil P

Reputation: 3190

Azure SQL Data Warehouse - parameterising an SSIS source query

I want to query my Azure DW as part of an SSIS data flow. I'm using an oleDb source to do this, however when I try to configure a parameter, I get the below error.

Stored procedure sp_describe_undeclared_parameters does not exist or is not supported. (Microsoft SQL Server Native Client 11.0)

Is this possible? Do I need to dymanically generate the query instead?

Upvotes: 2

Views: 980

Answers (1)

wBob
wBob

Reputation: 14379

Use the ADO.net provider to connect to Azure SQL Data Warehouse instead.

As per here, OLEDB is not fully supported, although you may find you can connect, the full set of features, including parameterising statements, is not supported:

Successful package execution

To parameterise the statement, use a combination of package-level parameters, SSIS variables and expressions. In my example, I'm using a package-level parameter called dimDate which is an Int32. I then use an SSIS variable called @[User::sql] to concatenate the stored proc I want to run and the parameter:

"EXEC dbo.usp_getDimDate " + (DT_STR, 8, 1252) @[$Package::dimDate]

SSIS variable with expression

Finally, make the SSIS variable the source fro the ADO.net SqlCommand using the Expressions section of the task:

ADO.net SqlCommand with expression

Upvotes: 1

Related Questions