Sql-server – Creating an SSIS package that uses a stored procedure

dtssql serverstored-procedures

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.

Best Answer

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

Related Solutions

Sql-server – Another BCP Export [Fail]ure

As I wrote in the comment to your previous post:

the bcp syntax look ok. I double checked with BOL and the only limitation for bcp is that if you use a procedure all tables have to exist prior to bcp being executed

No temporary tables or bcp will fail!

Update 1: Here is an example on how to exclude columns in the format file (*.fmt) for the bcp utility:

9.0
3
1       SQLCHAR       0       7      "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""       0     Col2         ""
3       SQLCHAR       0       100    "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Note the zero (0) in the length for Col2. You can find this example in the BOL of SQL Server 2008 at:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/30e0e7b9-d131-46c7-90a4-6ccf77e3d4f3.htm

Or search for Using a Format File to Skip a Table Column in BOL.

Sql-server – How to avoid delimiter character in BCP export

I managed to make this work by combining all columns into one single string from SQL and return it to BCP tool.

Best Answer

Related Solutions

Sql-server – Another BCP Export [Fail]ure

Sql-server – How to avoid delimiter character in BCP export

Related Question