July 14, 2011 at 11:54 pm
I was facing an issue while exporting data to a flat file from OLEDB source which contained a stored procedure,
SP
Create proc USP_Someproc
as
begin
exec USP_write_into_log
begin transaction
Some DML operations on table_a
Commit
select column_name from table_a
end
I have loads of SPs/packages that have similar structure.
If I give the SP directly in the OLEDB source I get an error stating "No column information was returned by the SQL command"
I tried short circuting technique to create the structure up front, something like
Create proc USP_Someproc
as
begin
if (1=1)
Select '' as column_name
exec USP_write_into_log
begin transaction
Some DML operations on table_a
Commit
select column name from table_a
end
that didn work for a few scenarios, so as a final resort I had to funnel the output of the final select statement and use that in the oledb source's sql command.
This worked fine. Recently I got a suggestion stating, Script component can be used as a source and the export can be done without introducing a temp table.
Iam a newbie to SSIS and .net and i am not fully aware of the functionality of the Script component. Are there any drawbacks in using it compared to the Temp table approach. Please advice.
I am using SSIS 2008 in a 64 bit environment.
July 15, 2011 at 12:27 am
The reason for the initial error is that a stored procedure doesn't have an explicit metadata contract, such as a view or a table-valued function. But SSIS does expect metadata from the source query. Hence the error.
You can try adding SET FMTONLY ON and SET NOCOUNT ON to your stored procedure, or replace the stored procedure with a table-valued function.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2011 at 1:12 am
July 15, 2011 at 1:36 am
Read this blog post for more background information on your problem:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2011 at 3:15 am
I followed a similar approach to resolve this... Just that instead of using a TVF I inserted the values directly into a temp table and referenced it [the temp table[with the retain same connection property set to true]... ] in the oledb source[mainly because i was not allowed to create new permanent objects in the DB]that works fine... as i mentioned earlier the question is, there are suggestions to switch to a script component instead of these changes in the SP...
From what i understand abt the script component, we need to use a .net code and the drive needs to be ADO.net driver instead of OLEDB driver. and the .net code does a row by row insert to the buffer and exports the data to the destination
Is it wise to move to a script component to avoid the usage of temporary tables or stick to the temporary table approach to avoid RBAR
July 15, 2011 at 3:18 am
Sriram.RM (7/15/2011)
I followed a similar approach to resolve this... Just that instead of using a TVF I inserted the values directly into a temp table and referenced it [the temp table[with the retain same connection property set to true]... ] in the oledb source[mainly because i was not allowed to create new permanent objects in the DB]that works fine... as i mentioned earlier the question is, there are suggestions to switch to a script component instead of these changes in the SP...From what i understand abt the script component, we need to use a .net code and the drive needs to be ADO.net driver instead of OLEDB driver. and the .net code does a row by row insert to the buffer and exports the data to the destination
Is it wise to move to a script component to avoid the usage of temporary tables or stick to the temporary table approach to avoid RBAR
I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2011 at 3:22 am
I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.
Unfortunately there are datasets that return close to 2 to 3 million rows.. however even in that scenario, wouldn the temp table out perform the .net code?? or is it the other way around...
July 15, 2011 at 3:25 am
Sriram.RM (7/15/2011)
I would certainly try to avoid the RBAR. It is notoriously slow. As long as your dataset isn't that big that it chokes the tempdb with the temp table, I would go for the temp tables.
Unfortunately there are datasets that return close to 2 to 3 million rows.. however even in that scenario, wouldn the temp table out perform the .net code?? or is it the other way around...
Temp tables for sure 🙂
Unless you want 3 million different transactions in your databases. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 15, 2011 at 3:44 am
🙂 thanks... and as a side note... the Script component starts throwing timeout errors for long running queries and setting the timeout in the properties of the connection manager doesnt help... I read it somewhere stating the timeout property has to be set both in code as well as in the properties... I have no clue on how to set that property in the code though...
July 11, 2013 at 2:42 pm
I had the same problem that SSIS couldn’t see the column even I can previewed the field in the SSIS.
Due to lack of permission, I couldn’t use stored procedure but t-sql script in SSIS and there were 3 temp tables in my t-sql script. I tried the 3 options in this link – http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/ great tips but still didn’t work. So, I used the combination of options2 and options 3 in the link and changed my connection manager to ADO NET from OLE DB. Finally, got it working.
Thanks for all the great tips!:-P
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply