0

I am executing a Powershell script using a SSIS Process task. The .PS1 code converts .xlsx file to .csv file. The package is running fine in local machine using BIDS, but by using the agent job the package is generating CSV file with 0KB.

$strFileName = "\\Server\Data\Test.xlsx"
$strSheetName = 'Page 1$'
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';"
$strQuery = "Select * from [$strSheetName]"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()
$da = New-Object system.Data.OleDb.OleDbDataAdapter($sqlCommand)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)
$dt | Export-Csv \\Server\Data\Test.csv -NoTypeInformation
$objConn.close()
p2k
  • 2,126
  • 4
  • 23
  • 39
  • are you running in 32 bit mode or 64 bit? – billinkc Sep 01 '15 at 14:43
  • Running the 64 bit of Powershell. – p2k Sep 01 '15 at 15:46
  • Switch it to 32 bit, bet it works – billinkc Sep 01 '15 at 15:47
  • While I was trying to run it with 32 bit, there were some error due to the OLEDB provider. Using 64 Bit, I tried to run the package using my ID by creating Proxy and it worked fine because I am the admin of the server. But I want to run with the APP_ID. What permission is missing with the APP_ID as it is creating CSV file with no data? – p2k Sep 01 '15 at 16:47

0 Answers0