I am using an LOEDB 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 was perseent in DTS 2000, but it doesn't seems to be
working in SSIS. Is there any other alternative to make it work.

Hello Sumanta,

Please see my reply to your other version of this question. please only
post the same question with one subject. It stops one person answering on
one thread and another on the other and they are effectively answering the
same post


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