How to run dynamic SQL in SSIS without specifying the result set?

Question

I am working on an ETL solution in SSIS. However, I have had a problem regarding the way dynamic SQL is executed with the SSIS-Toolbox of Visual Studio. I have a data flow, in which after performing a comparison of the data loaded in two tables (Stage Vs Dw_bi), rows and columns are added or edited so that the information is 100% matched.

The data that I have to load, have a number of columns that are fixed, they are not going to change, but there is another part of columns that is possible that they change with the time. In this sense, the columns that will be in the insert Into will not always be the same. To automate this task as much as possible, I used Dynamic SQL to create a Query that extracts from a table called parameters those columns that correspond to the part of columns that vary, to build a dynamic query that takes only the necessary columns. This code when I run it in SQL management Studio, runs without any problem and executes correctly. However, when I try to put this same code in an OLEDB COMMAND task in SSIS, I get the following error:

The code uses the creation of variable statements such as @SQL as a string, then executing it with the EXEC clause sp_executesql @sql. In addition, it is necessary to use parameters defined with ? to be taken during the query runtime, making the code even more dynamic. This makes that for example, the results have different number of columns, and of different type.

So, I am trying to figure out how to run dynamic code in SSIS, without the need to define the result set using the WITH RESULT SETS() clause. That is, the result set, as it happens in the management studio, should take the same structure of the query data, without the need to be explicit in the structure with the with result sets clause.

I am trying to figure out how to run dynamic code in SSIS, without the need to define the result set using the WITH RESULT SETS() clause. That is, the result set, as it happens in the management studio, should take the same structure of the query data, without the need to be explicit in the structure with the with result sets clause.

Answer 1

This makes that for example, the results have different number of columns, and of different type.

An SSIS data flow task is not for you. Period. Full stop.

SSIS Data Flow Tasks works extremely well at moving industrial quantities of data about. It does this by keeping as much as it can in memory. It "knows" how much memory each row is going to consume because the definition of a row of data is set at design-time aka I have Visual Studio open and am building the package.

There is no generic source to sink concept in SSIS. Azure Data Factory? absolutely yes but an SSIS data flow is not an option if the number of columns change or their data types change.

For completeness, you can use map run-time values in, that's totally allowed.

SELECT * FROM dbo.MyTable WHERE A.Col1 = ?

and then one clicks the parameter mapping tab and I'd then connect that to User::Variable1

You can probably also sp_executesql depending on your use case with parameters. Say you built your query to take the shape of

SELECT * FROM dbo.MyTable WHERE A.Col1 = ?

or

SELECT * FROM dbo.MyTable WHERE A.Col1 = ? and A.Col2 = ?

I think I remember that you could it look like

EXECUTE sp_executesql N'SELECT * FROM dbo.MyTable WHERE A.Col1 = @pval1', N'@pval1 int, @pval2 int', @pval1 = ?, @pval2 = ?;

Notice that even though I define the parameter pval2, I don't actually reference it in the query itself. That would allow me to create a master list of parameter mappings and then at run time, assuming the filtering clause was built dynamically, I can get different slicing behaviour at run time.

The width of the result set will always be the same but the depth can be as dynamic as I want it.