May 12, 2014 at 9:55 am
We're trying to create a package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing file.
This wouldn't normally be a problem, as we just plug in the query and it runs, but this time everything was complicated enough that we chose to approach it with a stored procedure employing temporary tables. How can I go about using this in a package? I tried going the normal route with the Wizard and then plugging in EXEC BlahBlah.dbo... It did not care for that:
The Statement could not be parsed. Additional information: Invalid object name '#DestinyDistHS'. (Microsoft SQL Server Native Client 10.0)
Can anyone guide me in the right direction here?
Thanks.
May 12, 2014 at 10:20 am
What do you mean by "the Wizard"? The import/export data wizard in SSMS?
I think what you will need to do is to code the package in BIDS/SSDT-BI and when you create the data flow set the Delay Validation property to true. What's happening is SSIS is trying to make sure the temp table(s) exist before compiling the package, setting DelayValidation = true delays validation until run-time which means that the temp table(s) will exist since the SP creates them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2014 at 11:05 am
Are you using SSIS 2012?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 12, 2014 at 2:29 pm
Jack Corbett (5/12/2014)
What do you mean by "the Wizard"? The import/export data wizard in SSMS?I think what you will need to do is to code the package in BIDS/SSDT-BI and when you create the data flow set the Delay Validation property to true. What's happening is SSIS is trying to make sure the temp table(s) exist before compiling the package, setting DelayValidation = true delays validation until run-time which means that the temp table(s) will exist since the SP creates them.
DelayValidation is not a silver bullet when it comes to temp tables. You also might want to set RetainSameConnection to true on the connection manager. The connection manager can create multiple connections and if the connection that created the temp tables is dismissed, no other connections can use the temp table because it will already be removed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2014 at 6:19 am
Koen Verbeeck (5/12/2014)
Jack Corbett (5/12/2014)
What do you mean by "the Wizard"? The import/export data wizard in SSMS?I think what you will need to do is to code the package in BIDS/SSDT-BI and when you create the data flow set the Delay Validation property to true. What's happening is SSIS is trying to make sure the temp table(s) exist before compiling the package, setting DelayValidation = true delays validation until run-time which means that the temp table(s) will exist since the SP creates them.
DelayValidation is not a silver bullet when it comes to temp tables. You also might want to set RetainSameConnection to true on the connection manager. The connection manager can create multiple connections and if the connection that created the temp tables is dismissed, no other connections can use the temp table because it will already be removed.
Yup, that's true, I just looked at this specific instance it looks like a single task where the temp table(s) are created in the task.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2014 at 7:45 am
unless you crate ##temptables which are global and can be shared by different connections.
May 13, 2014 at 7:47 am
aaron.reese (5/13/2014)
unless you crate ##temptables which are global and can be shared by different connections.
It doesn't matter if the connection that creates the temp tables is disconnected before any other connection can reference the temp table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2014 at 8:00 am
Koen Verbeeck (5/13/2014)
aaron.reese (5/13/2014)
unless you crate ##temptables which are global and can be shared by different connections.It doesn't matter if the connection that creates the temp tables is disconnected before any other connection can reference the temp table.
Koen, as usual, is correct. From BOL:
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
Also, global temporary tables can cause failures if 2 different sessions try to run code creating the same global temporary table. In SSMS if I run:
CREATE TABLE ##Test(id INT);
in session 1 and then open session 2 and run it again, session 2 will get this error:
Msg 2714, Level 16, State 6, Line 1
There is already an object named '##Test' in the database.
Sure I can check for existence in before creating the table, but then I also need to make sure I'm putting in some kind of session identifier to make sure each session is only using the data it created. And, if I'm going to do that I should probably just use a permanent "work" table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply