I have a powershell script which connects to database using ODBC connection string and produces a excel output. This excel output is then emailed to desired recipients by the script.
The script when runs from powershell directly works as expected.
However when the same script is scheduled in tidal, it generates empty excel file without any data.
Do you know why this is happening? Looks like either it is not probably using ODBC properly or not able to handle excel functionality.
e.g. I have been using following at high level:
$SQL=@"SELECT * FROM TABLE"@
$connectstring = "DSN=$DSN"
$conn = New-Object System.Data.Odbc.
$cmd = New-Object system.Data.Odbc.OdbcCommand($
$da = New-Object system.Data.Odbc.
$DataSetTable = New-Object system.Data.datatable
$null = $da.fill($DataSetTable)
## ---------- Working with Excel ---------- ##
## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;
#$xlsObj = New-Object -Com Excel.Application;
## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows)
foreach ($Coln in $getColumnNames)
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = `
$rowData++; $ColData = 1;
#Create a Excel file to save the data
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
New-Item "$DirectoryToSave" -type directory | out-null
## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
## ---------- Saving file and Terminating Excel Application ---------- ##
## - Saving Excel file - if the file exist do delete then save
$xlsFile = "$DirectoryToSave$filename.
if (Test-Path $xlsFile)
Appreciate your help in this regard!
Sometimes it depends on how the PS script is being called. There are some windows security models that we need to handle when calling PS scripts from Tidal
Thanks for your reply!
Please see the screen shot of the job (Configured) attached. The powershell is working fine when I run through central server(windows server 2012).However, through tidal it's generating empty excel file without any data.
Ok. The execution policy setting looks alright to me.
Few more things that I would verify :
- are you using the right version of the powershell(32 bit/64 bit) on both instances ?
- the user id under which the Tidal agent runs, is that different from the one that runs the PS manually ?
- on the server, have you created a batch script, which calls the powershell within it ? such as powershell.exe -ExecutionPolicy bypass -File script.ps1
1. I am using the 64 bit version of the powershell on both instances.
2. The user_id under which the Tidal agent is running is the same as for the PS when run manually.
3. I also created the bat script for calling power shell. This is still not giving me desired results. Please find attached screen shot of bat Job
The server on which the agent is running, is MS Excel installed ? I am guessing yes from your comments.
Can you please try the suggestion from the posts below
You have to create a folder (or two on a 64bit-windows):
Thanks for the suggestions. Lack of credentials took me longer time to create the following folders
as advised by you. However, tidal is still generating blank excel.
Please note if I run the powershell script directly after logging in under same user, it works as intended.