3

We're trying to make an SSIS 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 DTS 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.

  • Sorry -- old terminology floating around the workplace. We are working with SSIS. I'm picking this up as I go so I just ended up using the term I heard in the office. – Victor May 12 '14 at 15:41
  • 2
    in your stored procedure try SET FMTONLY OFF;SET NOCOUNT ON. Also at the package level - set the package property to use Delayed validation = TRUE. That should help you. – Kin Shah May 12 '14 at 15:56
  • Okay. I did everything listed. I changed ValidateExternalMetadata to False (did not see a direct reference to Delayed Validation)... Everything looked like it was going to work, but when I tried hitting okay to get out of the OLE DB Source Editor, I got a Microsoft Visual Studio Error: Eror at Data Flow Task... Again, "Invalid Object Name '#Destiny...'" – Victor May 12 '14 at 16:14
  • Are you defining the temp table inline or with a SELECT INTO statement? If you're using SELECT INTO, try doing a CREATE/INSERT setup instead. The problem is that SSIS is trying to parse the table and since it can't find a schema it can't build metadata. @Kin's suggestion to delay validataion will work but only once you have metadata actually assigned the first time 'round, in design mode. – Valkyrie May 12 '14 at 16:20
  • Oh! Go to the properties for the Data Flow Task itself to set Delay Validation to true. – Valkyrie May 12 '14 at 16:20
2

If you are calling the temp table from a different task try this

https://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.