Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSISDB deployed package run as windows user Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 6:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 93, Visits: 165
Hi

I am trying to run a SSISDB deployed package by windows user but as per My requirements I cannot directly login as windows user to server as job is being called from unix
So I tried using SQL agent job called by sql user and putting the following code in stored procedure with execute as windows users
the stored procedure is called from the job
the code is standard execution method from sql

EXEC  [SSISDB].[catalog].[create_execution] @package_name=N'abc.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'pqr', @project_name=N'abc', @use32bitruntime=False, @reference_id=Null

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id

But it is not able to access a shared drive part of package code during execution
but if the package is directly run as windows user this does work fine

Any idea


------------------------------------------------------------------------------------

Ashish

The journey to knowledge is knowledge of journey

Why so serious
Post #1491754
Posted Thursday, September 5, 2013 9:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 93, Visits: 165
now I have even tried putting stored procedure in ssisdb to avoid multi database clash but still the issue
The job is called correctly but when it comes to access shared drive it cant though it does have access to it


------------------------------------------------------------------------------------

Ashish

The journey to knowledge is knowledge of journey

Why so serious
Post #1492032
Posted Friday, September 6, 2013 1:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
I would simple schedule the package with a SQL Server Agent job (use the SSIS jobstep) and use a proxy.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492059
Posted Friday, September 6, 2013 2:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 93, Visits: 165

Hi

I am not able to use SQL agent as this job has to be called be unix box and so cannot log in as windows user or proxy user

So I tried execute as in stored procedure but if I explicitly mention windows user it does call the package but cannot access share folder through package



------------------------------------------------------------------------------------

Ashish

The journey to knowledge is knowledge of journey

Why so serious
Post #1492078
Posted Friday, September 6, 2013 3:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
The SSIS package needs to be stored and executed somewhere right? Create a SQL Server Agent job on that machine and start the job from the Unix box.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse