Update global variable with SQL command result
Todd asked 6 years ago
I have a simple map that is reading an excel spreadsheet and maps the output to a CSV result file. I have one task that runs before the document. That task is a SQL command…
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM eConnect_out
WHERE RTRIM(DOCTYPE) = RTRIM(‘_DOCTYPE’)
AND RTRIM(INDEX1) = RTRIM(‘_INDEX1’)
AND RTRIM(INDEX2) = RTRIM(‘_INDEX2’)
)
BEGIN
DELETE FROM eConnect_out
WHERE RTRIM(DOCTYPE) = RTRIM(‘_DOCTYPE’)
AND RTRIM(INDEX1) = RTRIM(‘_INDEX1’)
AND RTRIM(INDEX2) = RTRIM(‘_INDEX2’)
SELECT ‘DELETED’
END
ELSE
BEGIN
SELECT ‘NOT FOUND’
END
When I run this for 5 documents where the middle was actually deleted I get..
1. (nothing)
2. NOT FOUND
3. NOT FOUND
4. DELETED
5. NOT FOUND
Everything was shifted down. The task results from the first document was not available until the 2nd document map, etc…
Any suggestions?
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM eConnect_out
WHERE RTRIM(DOCTYPE) = RTRIM(‘_DOCTYPE’)
AND RTRIM(INDEX1) = RTRIM(‘_INDEX1’)
AND RTRIM(INDEX2) = RTRIM(‘_INDEX2’)
)
BEGIN
DELETE FROM eConnect_out
WHERE RTRIM(DOCTYPE) = RTRIM(‘_DOCTYPE’)
AND RTRIM(INDEX1) = RTRIM(‘_INDEX1’)
AND RTRIM(INDEX2) = RTRIM(‘_INDEX2’)
SELECT ‘DELETED’
END
ELSE
BEGIN
SELECT ‘NOT FOUND’
END
When I run this for 5 documents where the middle was actually deleted I get..
1. (nothing)
2. NOT FOUND
3. NOT FOUND
4. DELETED
5. NOT FOUND
Everything was shifted down. The task results from the first document was not available until the 2nd document map, etc…
Any suggestions?
I ended solving this by writing the task code in C# and moving that logic into a calculated field and mapping that resulting value directly in the map.