My current aim is to write the results from a T-SQL stored procedure into a text file for future reference / error capturing. The SP has been coded so that at set points messages are written to a table which is returned at the end of the script, e.g.
DECLARE @Log TABLE
( ID INT IDENTITY(1,1)
, ErrorDescription VARCHAR(MAX)
INSERT @Log (ErrorDescription)
SELECT 'Server: ' + @@SERVERNAME + ' Database: ' + DB_NAME()
(Perform data manipulation tasks & write further messages to @Log)
SELECT COALESCE(ErrorDescription,'') AS ErrorDescription
ORDER BY ID ASC
This was quite happily working for me using a Data Flow (with a Flat File Destination) until we included a temporary table into the stored procedure. This so included so that the main SP controlling the data could be broken down into different work areas whilst sharing a key table list (the temp table)
Seeing as SSIS does not like temp tables in a SP, due to the result definition being unknown, I've hit a problem.
I've tried the following suggestions to work round this, without luck:http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspxhttp://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table
(SET FMTONLY ON)
I've not been able to work around that issue (and really don't like the option of using an actual table instead a temp table), so I moved onto a different method.
Instead I've been trying to use a SQL Task Editor with the @Log result table being written to a Variable, which can then be written. With this method I'm now getting an error saying:
The type of the value (DBNull) being assigned to variable "User::MetadataLogging" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
This is occuring even though the SP is returning a string (as far as I can tell - SP has XML parameters of >20MB when used in full). I have converted the @Log table into a single string using the following:
SELECT COALESCE(ErrorDescription,'') AS [text()]
ORDER BY ID ASC
FOR XML PATH('')
),'') AS Logging;
Ultimately does anyone have any suggestions on how to work round either of these issues or an alternative method on how to get a T-SQL Stored Procedure with a #table to log to a flat file? I've been struggling with this all day, so any input would be welcome.