SSIS Dynamic Query

Question

I am trying to execute the dynamic query in SSIS.

When I try the following statement

EXECUTE(@dynamicquery)

It throws error and suggests to use WITH RESULT SETS

But in my case the result schema also dynamic

EXECUTE(@query) WITH RESULT SETS ((@resultsschema));

And this is not correct syntax.

Is there any other way to achieve this problem?

Here is another way to do this http://www.rafael-salas.com/2007/11/ssis-mapping-parameter-inside-of.html#!/2007/11/ssis-mapping-parameter-inside-of.html — Darka, Feb 25 '15 at 07:29

score 2 · Accepted Answer · answered Feb 25 '15 at 10:05

You need to change your approach and find the proper way with what SSIS provides us. From your question I assume you want to execute a DML statement with a SQL statement that is only known at execution time based on variables' values. I suggest you to use expressions (Execute SQL Task -> Expressions -> SQLStatement). This is also assuming you want to do it in the Work Flow, not Data Flow. However, a dynamic SQL in the Data Flow is not so different.

Have a look to the following article which provides an example of a dynamic expression in the Data Flow, and then is followed with another one in the Control Flow: http://www.sqlsafety.com/?p=266

SSIS Dynamic Query

1 Answers1