Error:
The error occurred after running SSIS package which has two Execute SQL Task component out of that one is outside the “Foreach” loop container is performing the “Select” Query which the second is inside the “Foreach” loop container is performing “Insert”Query as shown in the image down below.
In order to diagnose the error student of MSBI training clicked on the Progress tab to see the error in more detail as shown in the image down below.
Here the error says that the Variable EmployeeData does not contain a valid data. It means that the “Foreach” loop container is executing first and then the Individual Execute SQL Task component is executing which is outside the “Foreach” container.
Now to understand the error more properly we will see what kind of operation we are executing. Here we want the Execute SQL Task to perform Select Query on a table and select the data and then Insert that data in another table using the Insert Query. Now here what is happening is that first the Foreach container is executing which is containing Insert Query and then the Execute SQL Task component is executing which is containing Select Query.
So no Select and directly executing the Insert is what giving us an error in first place.
Solution:
Even after reading the error when student was not able figure out faculty of
MSBI training provided solution to this error which was very simple. There was just a need to put a connection between the Execute SQL Task component and the “Foreach Loop” Container. For that drag output of Execute SQL Task component and drop on Foreach Loop Container as shown in the image shown below.
By doing this connection the components will execute sequentially.
