for each variable getting unset

Question

for each variable getting unset

Sailor

SSCertifiable

Points: 5239
More actions
January 23, 2018 at 1:57 pm

#332361

So, inside a for each loop, I have various things that I am doing from an result set.
First off I go to a data flow to export data to an Excel Spreadsheet based on the parameter from the result set, which works.
After that I run a command line to send the email with the Excel as an attachment. The command line has a parameter mapped to it. This works.
The next step is to run a sql update with a parameter. This step does not work and seems to get 0 for the value.
If I move the email command to the third step and move the sql update to where the email runs, the SQL update works and the email gets 0.
What is causing the variable to get unset?
This is SQL Server 2016 SSIS and Visual Studio 2015. This worked on SQL 2008 ok for last six months.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sailor SSCertifiable Points: 5239 More actions · Answer 1

Sailor - Tuesday, January 23, 2018 1:57 PM
So, inside a for each loop, I have various things that I am doing from an result set.
First off I go to a data flow to export data to an Excel Spreadsheet based on the parameter from the result set, which works.
After that I run a command line to send the email with the Excel as an attachment. The command line has a parameter mapped to it. This works.
The next step is to run a sql update with a parameter. This step does not work and seems to get 0 for the value.
If I move the email command to the third step and move the sql update to where the email runs, the SQL update works and the email gets 0.
What is causing the variable to get unset?
This is SQL Server 2016 SSIS and Visual Studio 2015. This worked on SQL 2008 ok for last six months.

The exit code of the email was non zero.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

Sailor - Tuesday, January 23, 2018 1:57 PM
So, inside a for each loop, I have various things that I am doing from an result set.
First off I go to a data flow to export data to an Excel Spreadsheet based on the parameter from the result set, which works.
After that I run a command line to send the email with the Excel as an attachment. The command line has a parameter mapped to it. This works.
The next step is to run a sql update with a parameter. This step does not work and seems to get 0 for the value.
If I move the email command to the third step and move the sql update to where the email runs, the SQL update works and the email gets 0.
What is causing the variable to get unset?
This is SQL Server 2016 SSIS and Visual Studio 2015. This worked on SQL 2008 ok for last six months.

Difficult to help with so few details.
Have you run the package in debug mode to verify that the parameter is getting set in the first place? Note also that parameters are read-only and therefore their values cannot change during the execution of a package.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Sailor SSCertifiable Points: 5239 More actions · Answer 3

Phil Parkin - Tuesday, January 23, 2018 2:33 PM
Sailor - Tuesday, January 23, 2018 1:57 PM
So, inside a for each loop, I have various things that I am doing from an result set.
First off I go to a data flow to export data to an Excel Spreadsheet based on the parameter from the result set, which works.
After that I run a command line to send the email with the Excel as an attachment. The command line has a parameter mapped to it. This works.
The next step is to run a sql update with a parameter. This step does not work and seems to get 0 for the value.
If I move the email command to the third step and move the sql update to where the email runs, the SQL update works and the email gets 0.
What is causing the variable to get unset?
This is SQL Server 2016 SSIS and Visual Studio 2015. This worked on SQL 2008 ok for last six months.
Difficult to help with so few details.
Have you run the package in debug mode to verify that the parameter is getting set in the first place? Note also that parameters are read-only and therefore their values cannot change during the execution of a package.

The exit code of the email was non zero so it was not running the next "steps".

I have to figure out how to run in debug since i've been doing this since 2000. It seems like I never had all the components for it.