March 17, 2017 at 8:06 am
I have an SSIS package that creates a .xlsx from a query result. I then need to insert a new row 1 above the column headers and value columns 1 and 2. Then schedule it via the Agent.
I first did it with a script task, but it only worked in the Designer. The Agent couldn't do it. So then I did it with PowerShell. It works any/every way imaginable EXCEPT when run from the Agent. I tried as a Process Task in the package. I tried as a separate PowerShell Job Step. Each time I see an Excel process come up in Task manager and just sit there. Nothing happens in the .xlsx. This is a Windows Server 2012R2 with SQL Server 2014. The service account that runs SQL Server and the Agent is in the Administrator group. The folders where the Package, powershell script and Excel file live are Shared out with Everyone Full Control permissions. I've edited the Group Policy to allow local and remote signed PowerShell scripts.
Also - I've developed this same package using a Script Task on a Win2008R2, SQL 2008R2 server and it works from the Agent. This application is moving to Win2012, SQL2014 in a few months so I'm trying to get it working on what will eventually be the production server. Any help or insight would be greatly appreciated. I have working software that just won't work from the SQL Server Agent. I also installed 32bit Excel 2010 on this Win2012R2 server. I used 32bit because Excel and SSIS is typically a big pain with the bitness.
March 17, 2017 at 9:39 am
I have 2 thoughts:
1 - are you using a network share for the excel file and if so, are you using UNC paths?
2 - does your SQL Server Agent service account have permission to that file?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 17, 2017 at 10:13 am
The SQL Server service account is an Administrator on the machine.
All the files are on the SQL Server. Yes the folder is shared and yes I'm using UNC paths.
This PowerShell runs successfully from anywhere except an Agent Job.
## Load Excel file
$ExcelPath = '\\sqllsn101\Equifax\Transfer\Shell.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")
$xlShiftDown = -4121
$row = $ExcelWorkSheet.Cells.Item(1,1).entireRow
$active = $row.activate()
$active = $row.insert($xlShiftDown)
$ExcelWorkSheet.Cells.Item(1,1).Value2 = "1.00"
$ExcelWorkSheet.Cells.Item(1,2).Value2 = "I"
$row = $ExcelWorkSheet.Cells.Item(2,1)
$active = $row.activate()
$Excel.DisplayAlerts = $false
$Excel.ScreenUpdating = $false
$Excel.Visible = $false
$Excel.UserControl = $false
$Excel.Interactive = $false
$Excel.Save()
$Excel.Quit()
March 17, 2017 at 11:12 am
Randy Doub - Friday, March 17, 2017 10:13 AMThe SQL Server service account is an Administrator on the machine.
All the files are on the SQL Server. Yes the folder is shared and yes I'm using UNC paths.
This PowerShell runs successfully from anywhere except an Agent Job.
## Load Excel file
$ExcelPath = '\\sqllsn101\Equifax\Transfer\Shell.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")
$xlShiftDown = -4121
$row = $ExcelWorkSheet.Cells.Item(1,1).entireRow
$active = $row.activate()
$active = $row.insert($xlShiftDown)
$ExcelWorkSheet.Cells.Item(1,1).Value2 = "1.00"
$ExcelWorkSheet.Cells.Item(1,2).Value2 = "I"
$row = $ExcelWorkSheet.Cells.Item(2,1)
$active = $row.activate()
$Excel.DisplayAlerts = $false
$Excel.ScreenUpdating = $false
$Excel.Visible = $false
$Excel.UserControl = $false
$Excel.Interactive = $false
$Excel.Save()
$Excel.Quit()
The SQL Agent user needs to be a domain user if that UNC path is not local.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2017 at 11:53 am
Yes, the service account running the Agent is a domain account and is also an Admin on the server. All files involved are local to the server.
March 17, 2017 at 12:38 pm
This is not quite clear from your post: did you try running the Agent job in 32-bit mode? (Link)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2017 at 12:51 pm
Yes, I did. In the package and also the Job's Execution Plan options. I also tried a Operating System Job and called dtexec from the x86 folder.
I created a new job to just run the PowerShell commands outside of the SSIS package and it just hangs. Excel opens in background but does nothing.
I have a Disk Space Monitor job I install on every one of my SQL Servers. It runs PowerShell commands. It runs successfully on this server.
So the Agent can run PowerShell. And my PowerShell script works because I can run it successfully outside of SQL Server. It just won't run from inside SQL Server.
March 17, 2017 at 1:06 pm
Sounds like you've done everything within reason to make this work.
If you have the package deployed to SSISDB, have you looked at its 'All Executions' report to see whether that offers any clues?
You could also have a look in Event Viewer for other clues.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2017 at 2:58 pm
I found a post where someone says they are able to edit Excel from a Script Task: https://www.sqlservercentral.com/Forums/FindPost1553479.aspx
In their VB code, they have Imports Microsoft.Office.Interop.Excel
That is not valid for me. Do I need to install something for that library to be available to me?
March 17, 2017 at 3:04 pm
Ok - I found that. Add a Reference. 15 or so years ago I was writing VB 5 apps. I don't know what I'm doing in this .Net vb editor, but I do remember adding a reference.
🙂
March 17, 2017 at 3:12 pm
what happens if you change the visible to true? You should be able to see Excel running then and any errors that are coming up.
Since excel starts, but it cannot open the file, I would expect that the SQL Server Agent Service account can't open the file for some reason.
The other thing you could do is throw in some debugging statements. Dump stuff to a text file on c:\temp for example after each line is executed and include information like current variable values and last thing run.
Also, it has been FOREVER since I last installed excel, but does it have any "first run" steps that it needs to process? I am wondering if those are popping up because the SQL Server Agent Service Account has not run excel before.
Does your powershell work if you log into the same server you are now as the SQL Server Agent Service account and run the powershell?
As for in your VB code, I am assuming you mean VB.NET and if so, did you include the references to the office libraries?
EDIT - just saw your reply:
heh... I still support a few VB6 apps :/. It is surprisingly difficult to get VB6 apps to compile on Windows 7. I made it mostly work. Crystal reports (not sure which version, but an ancient one... 8 I think?) does not play nice. Crashes VB6 if I try to modify a CR from within the UI.
Thankfully the apps are mostly static now and slowly being phased out.
Did you get your powershell sorted out or did you scrap it in favor of VB6?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 17, 2017 at 4:02 pm
Brilliant. I had no luck with the package but I made another Agent job that only runs the PowerShell commands on an existing spreadsheet.
Nothing opened, Excel starts in background. Kill Excel and this time I have an error message from the job; can't find the file path.
Did that a couple of times and... holy smoke it works!
I changed my script - added Set-Location as the first line, but had to use the Drive Letter; Set-Location E:\MyFolder.
Then changed the $ExcelPath to the same; $ExcelPath = 'E:\MyFolder\Myfile.xlsx'. (Funny - even with Visible=True noting opened)
So I can have my Job run Step1 SSIS package to create the file and Step 2 PowerShell to insert this header row.
Thanks much. Better part of two days spent banging my head against the wall trying to figure something out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply