SSIS Package Run via SQL Agent Prooducing Different Results

Question

SSIS Package Run via SQL Agent Prooducing Different Results

NoahWenger-2370 6

Hi community!

I am trying to execute an SSIS package in SSMS via SQL Agent. The package is configured to run a Powershell script. Wen I execute the package manually (in Visual Studio 2019 or in the SSISDB catalog), I get the correct results. When I manually execute the PowerShell script itself, I get the correct results.

I have attempted to use a proxy account to run the SQL Agent Job, but I am still not getting the correct results. Does someone have any knowledge or experience with this? I am on SQL Server 2017 CU20, Windows Server 2019, PowerShell 7.2.1.

I have tried mixing and matching other configurations....please help!

Thank you,
Noah

Olaf Helper 47,436 Reputation points

2022-03-02T07:54:45.277+00:00

but I am still not getting the correct results.

And what's the correct and the not correct result? What in detail is the PS script doing?
NoahWenger-2370 6 Reputation points

2022-03-04T17:58:51.463+00:00

Hi Olaf,

The PS script creates a .CSV file containing all the Windows Task Jobs with their details/schedules on the local server. When I execute the PS script manually via PowersSell 7.2.1, I get 146 rows in the .CSV file. When I execute the script task manually in SSIS, I get 146 rows. When I deploy the package to SQL Server 2017 (CU20), then manually execute the package in SSISDB catalog, I get 146 rows. When I schedule an Agent job to execute the SSIS package, I get 202 rows.

I have tried using a Proxy account connected to a credential to run the SQL Agent job. I made sure that the SQLSERVERAGENT user has access to the the folder where the .CSV is created as well PS script itself. I have also given other Windows Users full control on the folder and files. I changed some of the Windows Policy settings as per an article I read on this issue. Powershell 7.2.1 is the latest version. SSIS was upgraded to the latest version. I am deploying the packages using the package deployment model (instead of the project deployment model). I tried running the SQL Agent job in 32-bit instead of 64-bit runtime. I also tried configuring the settings of the SSIS package based on information I read....

Thank you!

Noah
MotoX80 36,291 Reputation points

2022-03-04T18:16:27.103+00:00

Which result is correct? Where did the extra 56 records come from? Is some selection criteria being ignored?

Did you add any diagnostic statements into the PS script? Is there a "select * from DB where" statement in the script? Did you display that, so it was visible in the transcript?

I have written code to query SQL databases, but I am not familiar with SSIS.
MotoX80 36,291 Reputation points

2022-03-04T19:52:21.597+00:00

Can you do a select count to get the total number of records in the table to verify that you are reading from the same database in each test?
NoahWenger-2370 6 Reputation points

2022-03-04T19:59:51.177+00:00

Hey Moto,

So I discovered that:

1) The count of 202 Task Jobs (not the 146 count) might be the correct count.
2) Depending what PS command I use to obtain the Windows Task Jobs information, it provides a different list of the Task Jobs:

Get-ScheduledTask | Get-ScheduledTaskInfo produces one set of results
schtasks.exe /query /V /FO CSV produces a different result set (mutually inclusive of the list created by Get-ScheduledTask Get-ScheduledTaskInfo)

I need to review the commands to determine which of these two commands to use, if any and if the 202 count is correct.

Thank you for your help!!!

Best,
Noah
MotoX80 36,291 Reputation points

2022-03-04T21:46:18.14+00:00
I missed your reply to Olaf where you mentioned Windows tasks. It would it have been helpful if you had mentioned that in your initial question. Everything else was SQL related so the assumption was that you were reading a database and getting different results. And sharing at least a part of your script would have also been helpful. Are you filtering the results based on task name? Otherwise it will return all tasks that are defined on the server.

Querying scheduled tasks will produce different results depending on if the account has admin access and the process is running elevated.

Add this to your script.

function Test-IsAdmin { ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator") } if (Test-IsAdmin) { "" "You are running with Administrator access." "" } else { "" "You do not have admin access." "" }

3 answers

Your answer

Olaf Helper 47,436 Reputation points

2022-03-02T07:54:45.277+00:00

but I am still not getting the correct results.

And what's the correct and the not correct result? What in detail is the PS script doing?
NoahWenger-2370 6 Reputation points

2022-03-04T17:58:51.463+00:00

Hi Olaf,

The PS script creates a .CSV file containing all the Windows Task Jobs with their details/schedules on the local server. When I execute the PS script manually via PowersSell 7.2.1, I get 146 rows in the .CSV file. When I execute the script task manually in SSIS, I get 146 rows. When I deploy the package to SQL Server 2017 (CU20), then manually execute the package in SSISDB catalog, I get 146 rows. When I schedule an Agent job to execute the SSIS package, I get 202 rows.

I have tried using a Proxy account connected to a credential to run the SQL Agent job. I made sure that the SQLSERVERAGENT user has access to the the folder where the .CSV is created as well PS script itself. I have also given other Windows Users full control on the folder and files. I changed some of the Windows Policy settings as per an article I read on this issue. Powershell 7.2.1 is the latest version. SSIS was upgraded to the latest version. I am deploying the packages using the package deployment model (instead of the project deployment model). I tried running the SQL Agent job in 32-bit instead of 64-bit runtime. I also tried configuring the settings of the SSIS package based on information I read....

Thank you!

Noah
MotoX80 36,291 Reputation points

2022-03-04T18:16:27.103+00:00

Which result is correct? Where did the extra 56 records come from? Is some selection criteria being ignored?

Did you add any diagnostic statements into the PS script? Is there a "select * from DB where" statement in the script? Did you display that, so it was visible in the transcript?

I have written code to query SQL databases, but I am not familiar with SSIS.
MotoX80 36,291 Reputation points

2022-03-04T19:52:21.597+00:00

Can you do a select count to get the total number of records in the table to verify that you are reading from the same database in each test?
NoahWenger-2370 6 Reputation points

2022-03-04T19:59:51.177+00:00

Hey Moto,

So I discovered that:

1) The count of 202 Task Jobs (not the 146 count) might be the correct count.
2) Depending what PS command I use to obtain the Windows Task Jobs information, it provides a different list of the Task Jobs:

Get-ScheduledTask | Get-ScheduledTaskInfo produces one set of results
schtasks.exe /query /V /FO CSV produces a different result set (mutually inclusive of the list created by Get-ScheduledTask Get-ScheduledTaskInfo)

I need to review the commands to determine which of these two commands to use, if any and if the 202 count is correct.

Thank you for your help!!!

Best,
Noah
MotoX80 36,291 Reputation points

2022-03-04T21:46:18.14+00:00

I missed your reply to Olaf where you mentioned Windows tasks. It would it have been helpful if you had mentioned that in your initial question. Everything else was SQL related so the assumption was that you were reading a database and getting different results. And sharing at least a part of your script would have also been helpful. Are you filtering the results based on task name? Otherwise it will return all tasks that are defined on the server.

Querying scheduled tasks will produce different results depending on if the account has admin access and the process is running elevated.

Add this to your script.

function Test-IsAdmin { ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator") } if (Test-IsAdmin) { "" "You are running with Administrator access." "" } else { "" "You do not have admin access." "" }

Answer 1

MotoX80 36,291

Add a transcript to the script to capture it's activity. See if it generates an error somewhere.

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.host/start-transcript?view=powershell-5.1

Start-Transcript -Path C:\windows\temp\Myscript.log  
Your script code goes here.  
Stop-Transcript

Answer 2

NoahWenger-2370 6

Thank you MotoX80. There were no errors recorded. While the transcript was recording information, I executed the SSIS package which executes the PowerShell script and there were no messages in the transcript.

MotoX80 36,291

Add diagnostic displays to verify that you see the data that you expect.

Start-Transcript -Path C:\windows\temp\Myscript.log.
"Script starting, looking for my folder." 
Test-Path c:\temp  -verbose                           # get verbose output from the cmdlet 
"Looking for directories"
$fldrs = Get-ChildItem c:\temp -Directory -Recurse
"I found {0} folders" -f $fldrs.count
Stop-Transcript

MotoX80 36,291 Reputation points

2022-02-28T21:13:14.553+00:00

While the transcript was recording information, I executed the SSIS package

Just to verify... you do understand that the Start-Transcript statements go into your PS script, correct?

Answer 3

NoahWenger-2370 6

Hey MotoX80,

I did not understand that the Start-Transcript statements go into my PS script...I added the statements to my PS Script and still when I execute the SSIS package via SQL Agent, I am getting the wrong results and no messages between the start and end of the transcript. The below text was copied directly from the transcript output file:

Transcript started, output file is (I removed the file path for security purposes)

PowerShell transcript end

....

MotoX80 36,291 Reputation points

2022-02-28T21:57:06.637+00:00

Did you add any diagnostic displays to your script? If you run the script interactively, do you get the same output in the log?

What is your script doing?
MotoX80 36,291 Reputation points

2022-02-28T23:24:28.397+00:00

Are you doing anything with network drives? That's usually the problem.

Look at the script that I posted. Use Test-Path, Get-Item and Get-Childitem to verify that the account that the script is running as can see the files that it needs to reference.

Share via

SSIS Package Run via SQL Agent Prooducing Different Results

3 answers

Your answer