I am using an OEDB source in my Data Flow. My Sql Command is a complex sql
batch logic instead of a single sql query, which looks like this:
"
SET NOCOUNT ON

Declare @Table1 Table(GroupId Varchar(10) NOT NULL,
GroupName Varchar(50) NOT NULL, TAT Int)

INSERT INTO @Table1 (GroupId, GroupName, TAT)
SELECT G.RowId, Convert(Varchar(50),G.Name),
Avg(DateDiff(mi,SR.Created,SR.CloseDate))
FROM dbo.MSGroup G
LEFT JOIN ServiceRequest SR
ON G.RowID = SR.GroupID
And SR.Priority = '1 - Immediate'
WHERE G.Name IN (?)
GROUP BY G.RowId, G.Name
"

Though this code works whithout using a parameter, it gives following error
when using a parameter: "Parameters cannot be extracted from the SQL command.
The provider might not help to parse parameter information from the command.
In that case, use the "SQL command from variable" access mode"

Its really hard yo use a Variable to store the entire complex sql command.
This funtionality works in DTS 2000, but it doesn't seems to be
working in SSIS. Is there any other alternative to make it work.

Hello Sumanta,

1. Can you not wrap this in a proc?
2. How about using Property Expressions for the Statement Source?



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com