How to get powershell script to pull excel file to sql server database when called by sql agent job?

%3CLINGO-SUB%20id%3D%22lingo-sub-1409964%22%20slang%3D%22en-US%22%3EHow%20to%20get%20powershell%20script%20to%20pull%20excel%20file%20to%20sql%20server%20database%20when%20called%20by%20sql%20agent%20job%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1409964%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%20lang%3D%22es-PE%22%3EStandalone%20powershell%20script%20for%20pulling%20excel%20to%20sql%20server%20works%20and%20also%20when%20calling%20from%20Task%20scheduler%2C%20but%20powershell%20script%20fails%20when%20called%20from%20Sql%20Agent%20and%20SSIS(Sql%20Server%20Integration%20Service).%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%20lang%3D%22es-PE%22%3EError%20message%20received%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193687i4E39B81C76FFB2DB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorLuisEspinoza_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorLuisEspinoza_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3ETo%20do%20that%20I%20created%20a%20COM%20object%20that%20takes%20a%20url(it%20can%20be%20used%20with%20a%20local%20directory%20path%20as%20well).%3C%2FP%3E%0A%3CP%20class%3D%22lia-align-justify%20lia-indent-padding-left-60px%22%3E%3CEM%3E%24Excel%20%3D%20New-Object%20-ComObject%20Excel.Application%3C%2FEM%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-align-justify%20lia-indent-padding-left-60px%22%3E%3CEM%3E%24workbook%20%3D%20%24Excel.Workbooks.Open(%24filePath)%3C%2FEM%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-align-justify%20lia-indent-padding-left-60px%22%3E%3CEM%3E%24Excel.Visible%20%3D%20%24false%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%222%22%3EAfter%20knowing%20the%20issue%2C%20based%20on%20websites%2C%20I%20cleaned%20the%20office%20cache%2C%20killed%20an%20excel%20process%20in%20the%20background%20prior%20to%20the%20execution%20of%20this%20task%2C%20but%20my%20efforts%20were%20futile.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%222%22%3EBesides%2C%20I%20split%20the%20whole%20powershell%20script%20into%20pieces%20and%20added%20to%20command%20pane%20in%20the%20settings%20for%20the%20step%20like%20the%20following%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20644px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193688i5F473006129C7F25%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%C3%A7%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%20lang%3D%22es-PE%22%3EKindly%20wait%20for%20your%20guidance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411968%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20powershell%20script%20to%20pull%20excel%20file%20to%20sql%20server%20database%20when%20called%20by%20sql%20agent%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676022%22%20target%3D%22_blank%22%3E%40LuisEspinoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello!%20You've%20posted%20your%20question%20in%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FCommunity-Discussion%2Fbd-p%2FCommunityQuestions%22%20target%3D%22_blank%22%3ECommunity%20Discussion%20space%3C%2FA%3E%2C%20which%20is%20intended%20for%20discussion%20around%20the%20Tech%20Community%20website%20itself%2C%20not%20product%20questions.%20I'm%20moving%20your%20question%20to%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fwindows-powershell%2Fbd-p%2FWindowsPowerShell%22%20target%3D%22_self%22%3EPowerShell%20space%20%3C%2FA%3E-%20please%20post%20PowerShell%20questions%20here%20in%20the%20future.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Microsoft

Standalone powershell script for pulling excel to sql server works and also when calling from Task scheduler, but powershell script fails when called from Sql Agent and SSIS(Sql Server Integration Service).

Error message received:

image.png

 
 

To do that I created a COM object that takes a url(it can be used with a local directory path as well).

$Excel = New-Object -ComObject Excel.Application

$workbook = $Excel.Workbooks.Open($filePath)

$Excel.Visible = $false

After knowing the issue, based on websites, I cleaned the office cache, killed an excel process in the background prior to the execution of this task, but my efforts were futile.

Besides, I split the whole powershell script into pieces and added to command pane in the settings for the step like the following:

image.pngç

 

Kindly wait for your guidance.

1 Reply
Highlighted

@LuisEspinoza 

Hello! You've posted your question in the Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the PowerShell space - please post PowerShell questions here in the future.