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.
6-5-2015-2-21-32-AM.jpg

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?
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
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.

Eugene ZCommented:
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 ...
0
Leo TorresSQL DeveloperAuthor Commented:
Yes, the package is in msdb. SQL version 2008 R2.


how do set up and use a ssis proxy.
0
Leo TorresSQL DeveloperAuthor Commented:
OK, thought this link was a good break down.
http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

USE msdb
GO
--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
@proxy_name=N'SSISProxy'
,@login_name=N'MyDomain\SomeUser'
--,@fixed_server_role=N''
--,@msdb_role=N''
GO
--View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GO

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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eugene ZCommented:
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)
0
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
@proxy_name=N'SSISProxy'
,@login_name=N'MyDomain\MyUserID'

Open in new window

0
Eugene ZCommented:
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
https://technet.microsoft.com/en-us/library/ms189064(v=sql.100).aspx

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

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

Open in new window

0

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
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.