Powershell SSIS Job Not Working Properly

I am at a loss here don't know what to try Next.

Here is the scenario.
I have i SSIS package that runs 1 Powershell command on 10 separate instances by 10 seperate process task running at the same time.

Each process task has a list of servers assigned to it. The script takes a server name and insert the last 5 reboot times for each server and insert them into a table.
There are 7000 Server when I run the package in debug mode it works with out a hitch. The process creates 25,000 entries into the reboot table for the 7000 servers passed to it
(Again this runs fine NO errors)

Here is the issue when I take that same package and upload it to the SQL server Store and call it from a SQL job on the same server it also does not error out it completes successfully. (No errors)

My issue is the results why I run Debug I get 25000 records for reboots of servers. When I run the Job that runs the same package I get 6758 server only.

Not sure whats going on and why the large discrepancy. I have the sql job running as me.

Any idea why I am getting this behavior and how do I troubleshoot this?
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
for start: what is your sql server exact version edition and service pack ( It'd nice to have latest serve pack that may "fix" you issue)

..can you try to upload this pack  into msdb?

I'm not sure what your pack is actually doing
but I's check permissions of the sql agent account (If your job is running using it ) vs you login that you have probably use in the dev.
Your login may have more right vs sql to do what you need.
if you login has more rights - try to set ssis proxy and  use for your job step or adjust sql agent\use service account ...
Leo TorresSQL DeveloperAuthor Commented:
Yes, the package is in msdb. SQL version 2008 R2.

how do set up and use a ssis proxy.
Leo TorresSQL DeveloperAuthor Commented:
OK, thought this link was a good break down.

USE msdb
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
--View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy

Open in new window

The code produces this error looks like I cant use my account
'MyDomain\SomeUser' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

Open in new window

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
yes. you need to be sql sa.
 also if you can set windows sql agent service to use your login  it can help too ( no need proxy - just select ssis step use sqlagent account) .. in any case you need to have rights to set this or ask somebody (dba)
Leo TorresSQL DeveloperAuthor Commented:
Ya, I dont like the idea of my account being the used as the service account that's not goo practice. I will go with proxy because this is only one job.

Just so I am clear the DBA would need to run this portion of the code to give the proxy access to the user login correct?

EXEC msdb.dbo.sp_grant_login_to_proxy

Open in new window

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
yes, you are correct: it is not  good practice  -> DBA will take care of this (I was not sure if you have one :) )

about proxy:
you  need to set " Credential '
How to: Create a Credential (SQL Server Management Studio)

and after this proxy
Creating SQL Server Agent Proxies

 --for example 
USE [master]
CREATE CREDENTIAL [yourssis_crd] WITH IDENTITY = N'yourDomain\yoursrvAccount', SECRET = N'accountpwd'

USE [msdb]
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'yourssis_proxy',@credential_name=N'yourssis_crd', 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'ssis_proxy', @subsystem_id=11 --for ssis

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.