February 26, 2009 at 10:39 am
I am trying to query the results of processing individual files into a table:
CREATE TABLE dbo.Invoices
(InvoiceNo varchar(10) NOT NULL,
InvoiceDate smalldatetime NOT NULL,
CustomerNo varchar(20) NOT NULL,
DateShipped smalldatetime NULL,
CarrierType char(1) NULL)
I created a stored procedure for the query:
CREATE PROCEDURE [dbo].[usp_ReportSSIS_INV] AS
SELECT COUNT(InvoiceNo) as CntPkgs, 'Total Invoices' as QryType
FROM dbo.Invoices
UNION
SELECT COUNT(DISTINCT CustomerNo) as CntPkgs, 'Distinct Customers' as QryType
FROM dbo.Invoices
UNION
SELECT COUNT(InvoiceNo) as CntPkgs, 'No Ship Date' as QryType
FROM dbo.Invoices
WHERE DateShipped IS NULL or DateShipped = ''
The results of the stored procedure would be something like:
150 Total Invoices
10 Distinct Customers
3 No Ship Date
I setup a EXECUTE SQL Task as follows:
ResultSet: Full
Statement: EXEC dbo.usp_ReportSSIS_INV
Result Set:
Result Name Variable Name
0 User::var_InvResults
I have User::var_InvResults set as type OBJECT.
I then try to do the following in the SendMail task:
"Invoice Results for " + @[User::var_GetDate] + ": \r" +
@[User::Var_InvResults]
At which point I get the following messages:
The date type of variable "User::Var_InvResults" is not supported in an expression.
Reading the variable "User::Var_InvResults" failed withi error code 0xC00470D0.
Is there a way to pass the results of the stored procedure or the TSQL "union" statement without creating seperate variables for each query?
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 26, 2009 at 2:18 pm
From the research I have done so far, the answer appears to be no, it cannot be done.
You cannot simply try to cast the system.object variable into a string type or other type for the SendMail message body.
Another solution would be to process all of the recordset records into seperate variables (FOR EACH loop), which does not appear to be any different than creating multiple EXECUTE SQL tasks returning a single row and then assigning the row to variables.
At the moment I just created multiple SQL tasks and multiple variables.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply