October 10, 2012 at 5:25 pm
Hi!
I have a for each loop container that loops through a file and inside it isa data flow task and an execute sql task object with the following statement:
INSERT INTO EmpMaster
(EmpPath
,EmpID
,SOR
,EmpName
,FileName
,OpS)
SELECT
CRS.EmpPathAS EmpPath
, CRS.Emp_NOAS EmpID
, ?AS SOR
, CRS.Emp_NAMEAS EmpName
, ?AS FileName
, NULLAS OpS
FROM tmp_CRS CRS
LEFT JOIN EmpMaster EM ON CRS.Emp_NO = EM.EmpID
AND CRS.EmpPath = CM.EmpPath
WHERE CM.EMID IS NULL
The first and second "?" are variables of input type. The second "?" mapped to FileName column is actually the filename returned by the for each loop container.
The data flow task inserts the records from the files to the tmp_CRS table then executes the sql task object statement above.
The problem is with the second "?". On the first loop, the value changes to Filename1 and it inserts the records to EmpMaster just fine. On the second loop, where the value changes to "Filename2", it is able to insert the correct records except for the column FileName (the second "?") whose value is still Filename1.
Am I missing something or is the statement above something that the object can't handle? because when I changed the select part of the query from CRS.EmpPath to "test", etc. it works just fine. The filename value changes based on the files in the directory and inserted to the table alright.
Any help is much appreciated!
Thanks!
October 12, 2012 at 2:46 am
You can put a breakpoint on the Execute SQL Task. When you execute package, it will stop before executing the task. That way you can inspect the locals window and see if the variable actually changes value or not.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply