cancel
Showing results for 
Search instead for 
Did you mean: 
cancel

Powershell not working as intended from tidal

shsharma
Beginner
Beginner

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"@
$DSN="TEST"
$connectstring = "DSN=$DSN"
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
 $conn.open()
 $cmd = New-Object system.Data.Odbc.OdbcCommand($SQL,$conn)
 $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
 $DataSetTable = New-Object system.Data.datatable
 $null = $da.fill($DataSetTable)
 $conn.close()
 
 ## ---------- 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;
 $RowHeader++;
 };
 
 ## - 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) = `
 $rec.$($Coln.ColumnName).ToString();
 $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;
$xlsRng.EntireColumn.AutoFit();

## ---------- Saving file and Terminating Excel Application ---------- ##
 
## - Saving Excel file - if the file exist do delete then save
$xlsFile = "$DirectoryToSave$filename.xls"

 
if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};
--------------------------------------
Appreciate your help in this regard!

8 REPLIES 8

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.
 Please advise!

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
Please advise.