November 11, 2009 at 6:41 am
Hi friends,
For my application I need to extract data to flat file, the data is the result of a stored procedure execution.
When I used OLEDB as the source with SQLCommand as data access mode , after mapping the parameter the columns are not showing. This happened when I used an existing SP.
The same when I tried by creating one new sp its shows the columns...
What could be the reason... ? I'm sure that I have given the parameters in the same order and same as in the SP.
Regards,
MC
Thanks & Regards,
MC
November 11, 2009 at 6:53 am
Does it call a nested SP or return different result sets based off of logic in the SP?
Can you post the SP code?
November 11, 2009 at 7:16 am
Thanks,
No ,there is no nested Sp, based on the parameters it is doing some calculations and inserts the result into one #table, finally it selects the columns from the #table.
I'm sorry I can't post the sp code.
Regards,
MC
Thanks & Regards,
MC
November 12, 2009 at 5:39 am
hi friends,
I fount the reason for this unexpected thing!!! In side SP I have used #table and the final result I'm selecting from #table!!!
I don't want to create permanent table as solution.. 🙂
If any of you gets some other idea for this please let me know...
Thanks,
MC
Thanks & Regards,
MC
November 12, 2009 at 8:39 am
November 12, 2009 at 8:58 am
We had the same problem, to run sp from SSIS -OLE DB Source, we had the change #table to @table in the sp and it worked.
November 12, 2009 at 9:24 am
hi thanks,
With table variable it worked..!!!
so shall I conclude that with #table it wont work...?
Regards,
MC
Thanks & Regards,
MC
November 12, 2009 at 12:06 pm
November 12, 2009 at 5:33 pm
only4mithunc (11/12/2009)
hi thanks,With table variable it worked..!!!
so shall I conclude that with #table it wont work...?
Regards,
MC
You can make it work with a #table as well. It is trickier, but it will work.
However, it is probably easiest to leave it as a table variable with the required code being called from the stored proc.
To use a #temp table, the table needs to exist just long enough for you to map the columns. You also would need to set delayvalidation = True.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2009 at 9:41 am
That may be a better option depending on how many rows your SP is dealing with. The main difference between table variables and temp tables is that temp tables collect statistics and table variables do not. So if you are working with large recordsets, table variables are not your best bet.
Another option would be to set up a case in your code that checks for a rowcount of zero in your temp table and sets up a NULL select statement just to get the metadata back into your package.
For example, if your temp table looks like this:
CREATE TABLE #tmpTable (
Col1 int,
Col2 varchar(10),
Col3 bit,
Col4 datetime
)
Then at the end of your SP, include this:
IF (SELECT COUNT(*) FROM #tmpTable) = 0
SELECT CAST(NULL as int) as Col1,
CAST(NULL as varchar(10)) as Col2,
CAST(NULL as bit) as Col3,
CAST(NULL as datetime) as Col4
This way, SSIS can glean metadata from the SP even when the temp table does not yet exist.
November 16, 2009 at 8:00 am
As John said, the key is to trick SSIS. Sometime you want use temp tables in your stored proc and because of the size of the dataset you are working with, a table variable would be less than ideal.
The work around I use is to place a dummy statement at the beginning of my stored proc that returns the structure of the data set I want. This is placed inside an impossible condition. It will, of course, never run, but SSIS does not check the condition when it is creating the metadata for your data flow.
Inside Your stored proc, first statement after your BEGIN
IF 1 = 0
BEGIN
SELECT
CAST(NULL AS varchar(18)) AS AssetID,
CAST(NULL AS varchar(30)) AS ID_Number,
CAST(NULL AS varchar(3)) AS PriceSource,
CAST(NULL AS varchar(100)) AS PartyLongName,
CAST(NULL AS varchar(100)) AS Descr,
CAST(NULL AS varchar(18)) AS CurrCode,
CAST(NULL AS varchar(40)) AS ClosePrice,
CAST(NULL AS varchar(10)) AS PriceDate,
CAST(NULL AS varchar(10)) AS matDate,
CAST(NULL AS varchar(40)) AS CurrentCpn,
CAST(NULL AS varchar(18)) AS AssettTypeCode,
CAST(NULL AS varchar(18)) AS Ticker,
CAST(NULL AS varchar(18)) AS CountryOfIssue,
CAST(NULL AS varchar(18)) AS SovrClassCd,
CAST(NULL AS varchar(18)) AS SegmentCode,
CAST(NULL AS varchar(18)) AS AssetSubTypeCode
END
When you call the stored proc in your data flow, enter this into the SQL Command Text field:
SET NOCOUNT ON
EXEC procName
This is the simplest and fastest method I have found to get SSIS to find your metadata while using a stored proc as a data source.
There is another option to use, but the command escapes me at the moment. It will actually execute your stored proc in order to determine the meta data it will return. Since every time you make a change, it will rerun the proc, that is not good for productivity when you are trying to build a package.
November 26, 2009 at 9:21 am
Hi friends,
Thanks for all the reply, I was a little bit busy so was not able to reply.
In my sp ,I'm using temp tables many times, it is not for the final result alone. So in such cases what can we do ? can we do it by providing dummy temp table structure ?
I can use table variable , but there are chances of creating Non Clustered indexes on these tables, so if it is tables variable it will be a problem...
My sp is something like below.(by the below template I just want to show you that it is using more than one temp table and it has non clustered index)
So in such case how can we use this in SSIS.
create procedure sp_to_use_ssis
(
@sd int
)
as
begin
create table #t1
(
name varchar(30) ,
age int
)
create index ind_t1
on #t1(name)
insert into #t1
select name,
age
from maintable
create table #result
(
name varchar(30) ,
mark int
)
insert into #result
select
t1.name,
s.mark
from #t1 as t1
inner join secondtable as s
on t1.name = s.name
where s.id = @sd
end
Regards,
MC
Thanks & Regards,
MC
November 26, 2009 at 5:44 pm
The dummy statement is only for the dataset you are returning. Unless I misunderstand your situation, you want to use a stored proc as a source in your data flow. Whatever you do inside the stored proc doesn't matter. You are just using the dummy statement to tell SSIS the metadata of the dataset that the stored proc will be returning. What you do inside the guts of the stored proc doesn't really matter, as long as in the end you return that dataset
November 27, 2009 at 6:06 am
Thanks,
So you mean to say, no matter how many temp table I use inside the Sp? The dummy need to be ceated only for the final result?
Regards,
MC
Thanks & Regards,
MC
November 27, 2009 at 6:43 am
yes, that is correct
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply