Stored Procedure vs SQL Statement in SSIS Execute SQL Task and a Full Result Set
up vote
0
down vote
favorite
In the SSIS Execute SQL task, when I use a stored procedure to return data to a full result set, the resulting C# DataTable object only contains one row/column, with all values contained within one row/column. The rows are separated by commas, columns by dashes. However, when the same code executes via the SQL Statement, I get multiple rows/columns, so I can access via row[0].toString() etc.
This behavior occurs with both ADO.NET and OLEDB connections. Is this by design?
This link started me in the initial direction, but haven't found a definitive answer.
https://docs.microsoft.com/en-us/sql/integration-services/result-sets-in-the-execute-sql-task?view=sql-server-2014
ssis
asked Nov 9 at 19:45
Dayton Brown
90321428
add a comment |
up vote
0
down vote
favorite
In the SSIS Execute SQL task, when I use a stored procedure to return data to a full result set, the resulting C# DataTable object only contains one row/column, with all values contained within one row/column. The rows are separated by commas, columns by dashes. However, when the same code executes via the SQL Statement, I get multiple rows/columns, so I can access via row[0].toString() etc.
This behavior occurs with both ADO.NET and OLEDB connections. Is this by design?
This link started me in the initial direction, but haven't found a definitive answer.
https://docs.microsoft.com/en-us/sql/integration-services/result-sets-in-the-execute-sql-task?view=sql-server-2014
ssis
asked Nov 9 at 19:45
Dayton Brown
90321428
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In the SSIS Execute SQL task, when I use a stored procedure to return data to a full result set, the resulting C# DataTable object only contains one row/column, with all values contained within one row/column. The rows are separated by commas, columns by dashes. However, when the same code executes via the SQL Statement, I get multiple rows/columns, so I can access via row[0].toString() etc.
This behavior occurs with both ADO.NET and OLEDB connections. Is this by design?
This link started me in the initial direction, but haven't found a definitive answer.
https://docs.microsoft.com/en-us/sql/integration-services/result-sets-in-the-execute-sql-task?view=sql-server-2014
ssis
asked Nov 9 at 19:45
Dayton Brown
90321428
In the SSIS Execute SQL task, when I use a stored procedure to return data to a full result set, the resulting C# DataTable object only contains one row/column, with all values contained within one row/column. The rows are separated by commas, columns by dashes. However, when the same code executes via the SQL Statement, I get multiple rows/columns, so I can access via row[0].toString() etc.
This behavior occurs with both ADO.NET and OLEDB connections. Is this by design?
This link started me in the initial direction, but haven't found a definitive answer.
https://docs.microsoft.com/en-us/sql/integration-services/result-sets-in-the-execute-sql-task?view=sql-server-2014
ssis
ssis
asked Nov 9 at 19:45
Dayton Brown
90321428
asked Nov 9 at 19:45
Dayton Brown
90321428
edited Nov 9 at 20:25
asked Nov 9 at 19:45
Dayton Brown
90321428
asked Nov 9 at 19:45
Dayton Brown
90321428
asked Nov 9 at 19:45
Dayton Brown
90321428
90321428
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16
add a comment |
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53232346%2fstored-procedure-vs-sql-statement-in-ssis-execute-sql-task-and-a-full-result-set%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I've never seen that happen. You're probably doing something the wrong way.
– Tab Alleman
Nov 9 at 19:50
Please provide a sample query and perhaps a screenshot of how you have the Execute SQL Task configured. I too have never seen this before and I've seen quite a bit of SSIS ;)
– billinkc
Nov 9 at 21:36
@TabAlleman. You were right. I was doing something the wrong way. I forgot to change the stored procedure I was calling to the new version that had columns and rows. (ARGH!!!!!) Not sure if it's worth adding that in as an answer. Maybe it will help someone else.
– Dayton Brown
Nov 9 at 22:16