none
is the ssis powershell provider execute synchronous or asynchronous? RRS feed

  • Question

  • Hi , is this form of ssis execute from the ssis ps provider synchronous?   I didn't see any documentation online in my first attempt to research.

    (Get-ChildItem SQLSERVER:\SSIS\localhost\Default\Catalogs\SSISDB\Folders\Project1Folder\Projects\'Integration Services Project1'\Packages\ |
    WHERE { $_.Name -eq 'Package.dtsx' }).Execute("false", $null)

    Friday, November 22, 2019 2:22 PM

Answers

  • that form (get-childitem...execute...) of an ssis pkg execution from ps is definitely async.  I just tried it using a pkg with a delay.  I had to put import-module sqlserver ahead of it. 

    I took a quick look for documentation somewhere that might show how to set it to sync but didn't see anything. 

    The first param definitely has nothing to do with sync behavior.  it seems to be a bit mode execution setting.   

    The second is some sort of config collection (or something like that), so I dont know if there are possibilities there for setting a sync behavior flag.  

    I took a quick look at the Microsoft.SqlServer.Management.IntegrationServices Namespace.  I didn't see anything related to that method , its params or synchronized execution.  But I'm not sure what I was looking for. 


    • Edited by db042190 Friday, November 22, 2019 7:28 PM show the import command
    • Marked as answer by db042190 Tuesday, November 26, 2019 1:59 PM
    Friday, November 22, 2019 7:21 PM
  • Hi db042190,

    Thanks for posting here.

    Per your recent description, you could use dtexec command to achieve your needs.

    DTExec /ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" /SERVER "." /Envreference 2 /Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True  
    

    Integration Services Server and Project File

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by db042190 Tuesday, November 26, 2019 1:59 PM
    Monday, November 25, 2019 7:59 AM

All replies

  • It doesn't matter from where you start a Job/package (or how often you ask this), Jobs/packages runs asynchron.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 22, 2019 2:40 PM
  • thx Olaf, but packages do not run async from the ssisdb tools when a certain flag is set to synchronize. Nor do they run async from job steps because the automatically generated dtexec command for catalogued pkgs looks like this @command=N'... /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True .   My question stands. 
    Friday, November 22, 2019 2:50 PM
  • that form (get-childitem...execute...) of an ssis pkg execution from ps is definitely async.  I just tried it using a pkg with a delay.  I had to put import-module sqlserver ahead of it. 

    I took a quick look for documentation somewhere that might show how to set it to sync but didn't see anything. 

    The first param definitely has nothing to do with sync behavior.  it seems to be a bit mode execution setting.   

    The second is some sort of config collection (or something like that), so I dont know if there are possibilities there for setting a sync behavior flag.  

    I took a quick look at the Microsoft.SqlServer.Management.IntegrationServices Namespace.  I didn't see anything related to that method , its params or synchronized execution.  But I'm not sure what I was looking for. 


    • Edited by db042190 Friday, November 22, 2019 7:28 PM show the import command
    • Marked as answer by db042190 Tuesday, November 26, 2019 1:59 PM
    Friday, November 22, 2019 7:21 PM
  • Hi db042190,

    Thanks for posting here.

    Per your recent description, you could use dtexec command to achieve your needs.

    DTExec /ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" /SERVER "." /Envreference 2 /Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True  
    

    Integration Services Server and Project File

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by db042190 Tuesday, November 26, 2019 1:59 PM
    Monday, November 25, 2019 7:59 AM
  • thx Will. I tried running what you posted (left first param in for now) as shown below in ssms.  I got this error...

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '/'.

    I believe the error is on the first slash following the word/command dtexec.  Maybe I need a bridge like cmdshell between sql and .net like they show here https://stackoverflow.com/questions/11469457/run-executable-from-sql-server ?

    I'm also working on a definitive answer to the possibility of getting the ps get-children format working synchronously. 

    DTExec /ISSERVER "\SSISDB\testincremental\testincremental\xxtopowershelltest.dtsx" /SERVER "." /Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb /Par "$ServerOption::SYNCHRONIZED(Boolean)";True



    • Edited by db042190 Monday, November 25, 2019 1:52 PM more info
    Monday, November 25, 2019 1:09 PM
  • Hi db042190,

    The dtexec command prompt utility doesn't belongs to system command under cmd or powershell. It is installed under SQL Server path (<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn). Have you read the whole article?

    Installation Considerations on 64-bit Computers

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 26, 2019 8:33 AM
  • thx Will, I'm just asking how/where you would execute the command you posted?  I'll read your articles but I'm trying to get a sense from where (what context) such a command can be executed.  Maybe you can show how and from where you executed that command.      
    Tuesday, November 26, 2019 1:11 PM
  • Will, I didn't notice your original link under the image.   My bad. 

    it looks like one of 2 ways (3 if you count bash). Right from a command prompt like any other exe. or xp_cmdshell.  I'll try the latter and post back here.   


    • Edited by db042190 Tuesday, November 26, 2019 2:03 PM forgot about bash
    Tuesday, November 26, 2019 1:17 PM
  • yes, this worked and I wouldn't consider executing dtexec via a command line from a remote ps script even though that is possible too.   I think the original question was answered earlier.  My attempt to learn how (if its even possible)  to make the get-childitem ... to execute synchronously doesn't seem to be going anywhere but i'll follow up here if that effort turns up anything.  turning on xp_cmdshell supposedly has some security implications so we will take that into consideration too.  

    EXEC xp_cmdshell 'DTExec /ISSERVER "\SSISDB\testincremental\testincremental\xxtopowershelltest.dtsx" /SERVER "." /Par "$ServerOption::SYNCHRONIZED(Boolean)";True'

     
    • Edited by db042190 Tuesday, November 26, 2019 2:00 PM mention security issues
    Tuesday, November 26, 2019 1:58 PM
  • my attempt at https://andyleonard.blog/2015/11/the-synchronized-ssis-execution-parameter/?unapproved=24727&moderation-hash=3f9915880274f5d62e8c68060d11ef51#comment-24727 to learn how/if this command can be modified to run synchronously has gone unanswered.  If anyone knows the answer pls post here.  I tried to follow some of the links from here https://docs.microsoft.com/en-us/dotnet/api/microsoft.powershell.management.activities.getchilditem?view=powershellsdk-1.1.0 thru the execute method link but I started getting a headache.

    (Get-ChildItem SQLSERVER:\SSIS\localhost\Default\Catalogs\SSISDB\Folders\Project1Folder\Projects\'Integration Services Project1'\Packages\ |
    WHERE { $_.Name -eq '
    Package.dtsx' }).Execute("false", $null)

    Tuesday, December 3, 2019 8:24 PM