September 12, 2013 at 5:40 pm
I have a store procedure that uses temp tables and CTE . I am calling the stored procedure from Oledb source in data flow task in ssis. It gives me some error like " The metadata could not be determined because statment ' WITH CTE1 AS (...... unable to retrieve column information from the data source. Make sure the target table in the database is available. There seem to be dislike/disconnect between temp table and ssis oldb source using stored proc- anybody has any idea for resolving this?
Also i am trying to push data to a database that is in a completely different network. I can ping it but it is not visible through sql server management or ssis. how can dump data into a table in a different network-ed database?
September 13, 2013 at 12:41 am
An OLE DB Source needs metadata for the columns. Stored procedures don't have fixed metadata.
Using stored procedures inside an OLE DB Source component
I'd suggest to use a table valued function, but it doesn't support temp tables. (do you really need temp tables?) Or do you define the temp table in an earlier step of the SSIS package? In that case, you need to set RetainSameConnection to true on the connection manager.
You also might want to try to put a "dummy select" in your stored procedure. Something like this:
IF 1 = 0
BEGIN
SELECT col1, col2, ..., coln FROM myTable;
END
The dummy select returns the exact same metadata/columns as the stored procedure. When the OLE DB source scans the stored procedure for metadata, he might take the one from the dummy select and be satisfied. Worth checking out.
Regarding the database in a different network: you should use it's IP address in order to connect to it. And make sure the firewall doesn't block it of course.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply