March 7, 2017 at 10:42 am
Hi,
When i was trying to execute the SSIS package through command prompt below:
C:\Users>dtexec /f "C:\Users\Documents\Visual Studio 2015\Projects\SSISPackage\Package.dtsx" /Set "\Package.Variables[User::WorkingDirectory].Properties[Value]";"C:\\AzureDirectory\\Scripts\\" /Set "\Package.Variables[User::PSUserID].Properties[Value]";"xxx@gmail.com"
I am getting this error:
The arguments 'AzureUsers.ps1' to the -File parameter doesnot exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter.
Is there a way to tweak the code and get it executed.
Thanks in advance.
March 7, 2017 at 10:45 am
You see that error at the command line? Find the option that allows you to redirect the log output to a file and then post the contents of the error lines from that file.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 12:42 pm
sgmunson - Tuesday, March 7, 2017 10:45 AMYou see that error at the command line? Find the option that allows you to redirect the log output to a file and then post the contents of the error lines from that file.
Started: 2:33:23 PM
Progress: 2017-03-07 14:33:23.58
Source: Load Output into Database
Validating: 0% complete
End Progress
Progress: 2017-03-07 14:33:23.58
Source: Load Output into Database
Validating: 50% complete
End Progress
Progress: 2017-03-07 14:33:26.42
Source: Load Output into Database
Validating: 100% complete
End Progress
Progress: 2017-03-07 14:33:26.44
Source: Exexute PowerShell Script to get the Output Execute Process Task
Operation Complete: 0% complete
End Progress
The argument 'AzureUsers.ps1' to the -File parameter does not exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter.
Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
Error: 2017-03-07 14:33:26.72
Code: 0xC0029151
Source: Exexute PowerShell Script to get the Output Execute Process Task
Description: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-File "AzureUsers.ps1" "Test.csv" """ at "", The process exit code was "-196608" while the expected was "0".
End Error
Progress: 2017-03-07 14:33:26.72
Source: Exexute PowerShell Script to get the Output Execute Process Task
Operation Complete: 100% complete
End Progress
Warning: 2017-03-07 14:33:26.72
Code: 0x80019002
Source: Sequence Container
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
Warning: 2017-03-07 14:33:26.72
Code: 0x80019002
Source: PowershellAzureCSV
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:33:23 PM
Finished: 2:33:26 PM
Elapsed: 3.516 seconds
March 7, 2017 at 12:50 pm
Turns out the package tries to run PowerShell, only to discover that the file 'AzureUsers.ps1' doesn't exist in the location it was expecting to find it. So the portion of your log file that says this:The argument 'AzureUsers.ps1' to the -File parameter does not exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter.
Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
is the problem. Find and fix the path to the file as specified in the SSIS package.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 1:05 pm
sgmunson - Tuesday, March 7, 2017 12:50 PMTurns out the package tries to run PowerShell, only to discover that the file 'AzureUsers.ps1' doesn't exist in the location it was expecting to find it. So the portion of your log file that says this:The argument 'AzureUsers.ps1' to the -File parameter does not exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter.
Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.is the problem. Find and fix the path to the file as specified in the SSIS package.
The WorkingDirectory which I have provided contains the 'AzureUsers.ps1' file. Still it is showing 'AzureUsers.ps1' doesn't exists. Is there a way to tweak this command and make it work
Thanks.
March 7, 2017 at 1:17 pm
Okay, so how did you tell PowerShell to look in that directory?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 1:41 pm
I have saved power shell commands in 'AzureUsers.ps1' and trying to execute it SSIS by using Execute Process Task.
I have given
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: -File "AzureUsers.ps1" "Test.csv" ""
WorkingDirectory:
In expression, I have added WorkingDirectory as @[User::WorkingDirectory]
I have given WorkingDirectory(In which we should have powershell script) and PSUserID(AzureAD connectivity) as parameters.
This is how I designed a package.
when it gets executed it will save the PowerShell output into Test.csv file.
March 7, 2017 at 2:30 pm
Are you current on service packs for SQL 2016? I don't have a 2016 instance to check anything out with, but I do have a 2012 instance that I tried to do something similar with, and it always succeeds at attempting to run the AzureUsers.ps1 file I mocked up to just create a directory, but for me, while it never fails SSIS execution, it never creates the directory either, as I get the following error:
File C:\TEMP\AzureUsers.ps1 cannot be loaded because the execution of scripts is disabled
on this system. Please see "get-help about_signing" for more details.
At line:0 char:0
Thus I'm not sure where the problem lies, but is it possible that your execution context doesn't have access to the folder where the file is located?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 3:08 pm
I am using SQL server 2014. I have access to the folder. When I deploy the package and execute it through SSMS it getting executed. But when i'm trying to execute it through command prompt it showing this error.
In your case your SSIS execution is not failing either its not able to create csv file. Is that what you are saying?
If this is the problem. Include 'start-sleep -s 20' at the end of the PowerShell command and run the SSIS package. It hols 20 sec when powershell executes. Display's if there is any error.
March 8, 2017 at 6:47 am
As you posted in the SQL 2016 portion of the forum, I had presumed you were using that version of SQL Server. As to my case, my system will simply not allow a PowerShell script to run, and probably due to some kind of group policy over which I have no control. It does successfully find my .PS1 file, and that script is only supposed to create a folder, not create a csv file. However, that's a very different case from your situation, where you are apparently at least executing PowerShell, but then PowerShell can NOT see the script file. There are really only a couple of ways that can occur:
1.) The execution context for the SSIS package has been changed from the default by some property or configuration of the SSIS package.
2.) You opened the command prompt by clicking on Run As Administrator, which would change the execution context.
3.) A command line parameter provided to the SSIS package when it runs on a scheduled basis provides some input to what execution context it runs under and is not reproduced in your command line execution attempt.
4.) There's a permissions issue somewhere.
Generally, for an SSIS package, I avoid putting any kind of "sleep" elements into anything automated unless the nature of what it needs to do requires a loop where you wait some amount of time for something to occur and retry an action. User interaction is also generally a bad idea for automated scripts. Bottom line, however, is that one of the 4 things above is likely to be true. You may need to dig pretty deep to find it though.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 8, 2017 at 9:46 am
sgmunson - Wednesday, March 8, 2017 6:47 AMAs you posted in the SQL 2016 portion of the forum, I had presumed you were using that version of SQL Server. As to my case, my system will simply not allow a PowerShell script to run, and probably due to some kind of group policy over which I have no control. It does successfully find my .PS1 file, and that script is only supposed to create a folder, not create a csv file. However, that's a very different case from your situation, where you are apparently at least executing PowerShell, but then PowerShell can NOT see the script file. There are really only a couple of ways that can occur:1.) The execution context for the SSIS package has been changed from the default by some property or configuration of the SSIS package.
2.) You opened the command prompt by clicking on Run As Administrator, which would change the execution context.
3.) A command line parameter provided to the SSIS package when it runs on a scheduled basis provides some input to what execution context it runs under and is not reproduced in your command line execution attempt.
4.) There's a permissions issue somewhere.Generally, for an SSIS package, I avoid putting any kind of "sleep" elements into anything automated unless the nature of what it needs to do requires a loop where you wait some amount of time for something to occur and retry an action. User interaction is also generally a bad idea for automated scripts. Bottom line, however, is that one of the 4 things above is likely to be true. You may need to dig pretty deep to find it though.
Thanks sgmunson, I will dig into it. I appreciate your time and effort.
March 8, 2017 at 12:32 pm
SQL-Learner - Tuesday, March 7, 2017 1:05 PMC:\Users>dtexec /f "C:\Users\Documents\Visual Studio 2015\Projects\SSISPackage\Package.dtsx" /Set "\Package.Variables[User::WorkingDirectory].Properties[Value]";"C:\\AzureDirectory\\Scripts\\" /Set "\Package.Variables[User::SUserID].Properties[Value]";"xxx@gmail.com"
Verify that the package/job is running as the same user containing the ps1 file. I seen it a number of times over the years in which a developer creates something in a user profile folder, only to be surprised when the task looks in the user profile for the service account.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply