1

Topic: SSIS - execute SQL task. How to obtain the data from the temporary table?

Good afternoon;
I need to form "on the fly" the big team SQL for another's server (not SQL).
For this purpose I use Execute SQL Task that this command to splice from slices. An operating procedure:
1) I Fill SQLStatement.
Something like it:

-- Variable for operation
declare @my_command nvarchar (max);
-- We form team SQL (here there can be a lot of code)
set @my_command = ' select from tbl_main where (......) ';
set @my_command = replace (@my_command, ' old_val ', ' new_val ');
set @my_command = '..... ';
-- Here this  deduces final command SQL and I want to read it in SSIS a packet
select @my_command as col1;

3) On tab General I write that resultset should be SingleRow.
2) On tab ResultSet I show that the variable should read value of a column col1.
does not work. SSIS the packet does not want to read value of a column col1.
I already faced it is because of sampling "on the fly". I.e. here it: select @my_command as col1;
If there there was a real table all would be good. How to win??
Thanks.

2

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

Got out.
1) Created Data Flow Task.
2) Further "Ole DB Source" and there generated command SQL.
3) Added "Script component" in a mode "Destination" and appropriated values to variables.

3

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

To splice from slices dynamic sql, besides big - ,

4

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

User2155 wrote:

I already faced it is because of sampling "on the fly". I.e. here it: select @my_command as col1;

by no means... It normally works.
Other question where at you here "temporary table"?
Also I hope you understand, what nvarchar (max) it at all string?

5

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

User2155 wrote:

Good afternoon;
I need to form "on the fly" the big team SQL for another's server (not SQL).
For this purpose I use Execute SQL Task that this command to splice from slices.
...

And what for so, strange enough method. To eat much better and easier - is called variable expressions. You can define for a variable expression for calculation, and it will be calculated at reversal to a variable.

6

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

User2155;
To your initial question - if you use OLEDB connection manager output parameters are set as number of columns. That is in your case - set number of a column 0 and read it.
On names are able to read only ADO.NET connection manager, but there the problems.

7

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

Ferdipux wrote:

if you use OLEDB connection manager,
That output parameters are set as number of columns.
That is in your case - set number of a column 0 and read it.

and than names of fields did not please you?

8

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

Ferdipux;
About expression for variable is I use, but if either conversions difficult or components it is a lot of, all of them should be tired out beforehand in the variables and if besides they are not necessary any more what sense? As the expression code turns out absolutely not not read in this case and for its editing it is necessary to use exterior editors. And something to correct quickly it is absolutely not convenient.
Therefore sometimes Execute SQL Task or Script Task it is more preferable.

9

Re: SSIS - execute SQL task. How to obtain the data from the temporary table?

The grandfather;
Yes, you are right, should read. Probably, phantoms after SSIS 2005/2008, there were h'm, specificity...