handle the dynamic queries for Execute SQL Task in SSIS

Question

Post reply

handle the dynamic queries for Execute SQL Task in SSIS

vinod.km

SSC Eights!

Points: 971
More actions
November 22, 2007 at 12:09 am

#119630

In this mail, I want to clarify some doubts w.r.t. DTS . Could anybody please clarify me following questions?
Question 1: How to handle the dynamic queries for Execute SQL Task in SSIS. This was handled through Dynamic properties task in DTS.
I thought to handle it through variable and expression. But, it is limited to 4000 characters. So, queries bigger than this will create problem.
Question 2: How the parameters of the query written in Dynamic Properties task of the DTS is handled?
Eg: Following is the one of the dynamic query added to dynamic properties task as constant. I don’t know how the parameters mentioned here within <> parenthesis are resolved,because in DTS no active Script Task is used. The below mentioned dynamic query I have to use in Execute SQL Task of SSIS.Could you please clarify this?
- AUDIT SQL TASK
INSERT INTO ODS.dbo.StoreInventoryFilesProcessed
(NAPAStoreID, PostDate, InventoryDate, ReceivedDate, ProcessedDate, LoadStatus, LoadMessage, TotalRowsProcessed, InventoryFileName, InventoryDirectoryName)
SELECT
T.NAPAStoreID,
CONVERT(smalldatetime, T.AccountingDate),
CONVERT(smalldatetime, ' '),
T.Modified,
GETDATE(),
' ',
' ',
,
T.SINVFileName,
NULL
FROM
ODS_Stage.dbo.SG_SINV_FileList T
WHERE
T.SINVFileName = ' ';
IF @@ROWCOUNT > 0 BEGIN
UPDATE ODS_Stage.dbo.SG_SINV_FileList SET
Status = 'E'
WHERE SINVFileName = ' ';
END;
-- AUDIT SQL TASK
INSERT INTO ODS.dbo.StoreInventoryFilesProcessed
(NAPAStoreID, PostDate, InventoryDate, ReceivedDate, ProcessedDate, LoadStatus, LoadMessage, TotalRowsProcessed, InventoryFileName, InventoryDirectoryName)
SELECT
T.NAPAStoreID,
CONVERT(smalldatetime, T.AccountingDate),
CONVERT(smalldatetime, ' '),
T.ReceivedDate,
GETDATE(),
' ',
' ',
0,-- ,
REPLACE(T.StageTable, 'sg_', '') + '.dat',
NULL
FROM
ODS.dbo.StoreInventoryJobQueue T
WHERE
T.StageTable = 'sg_' + REPLACE(' ', '.dat', '');