Issues Running ExifTool command line via SQL Agent Job (xp_cmdshell) &/OR SSIS

[Fellhouse]

Hello All!

Thanks in advance for any help you can provide. I have looked through the forum and unfortunately cannot find an exact answer to the issue I am having. I have spent a day on it and am to the point where I feel like I need some help. I saw a thread that discussed issues with running ExifTool from VBA in Excel and there are some common issues, but the solution doesn't quite resolve the issue I am having.

Anyway, here is the issue. I am running a SQL Server database with an MS Access GUI. I am building out the forms on MS Access to add a feature for updating metadata on the content files we create. Anyway, the form user selects a folder on the local computer or more than likely on a network drive and pushes a refresh button. The button event grabs the file contents of the folder selected via VBA and then I am trying to get ExifTool to fire off on the server and output a CSV with specific tags on the files in that folder so I can import them for viewing on the form. I don't want to run ExifTool on the local computer as I don't want to have to install it on each users computer and I am unsure how to import a PERL module for use within the MS Access VBA environment. I played around with PowerShell a bit, but ultimately abandoned it because getting network resources in the PS session was too complicated. I could successfully run the process remotely if everything was local to the server, but couldn't access or write to network places. Ideally, I wanted to work within MS Access, but after all the issues I ultimately just created an SSIS dtsx package on the server that works perfectly when I run it from the Business Intelligence Dev Studio. The SSIS package uses a Execute Process Task that has the following settings:

Executable: C:\Windows\System32\cmd.exe
Arguments: /c c:\Image-ExifTool-10.64\exiftool.pl -sep "|" -ItemName -VariationImageStem -BrandName -UsageCategory -ContentDesignation -FileModifyDate -FileCreateDate -FileTypeExtension -ImageWidth -ImageHeight -csv "\\SERVER\DIRECTORY" > "\\SERVER\DIRECTORY\FILE.csv"

I have to use the cmd.exe because the exiftool syntax requires double quotes which won't work in the arguments because it wraps everything in double quotes at run time.

To fire off the SSIS Package I then created a SQL Agent job that uses information from the form to run the SSIS package via DTEXEC via xp_cmdshell. I use the DTEXEC because I am defining SSIS package variables in the command in order to have it process the specific requests of the form.

Anyway, I have other SSIS packages that fire off using this same method and no issues. From my testing, it appears that the xp_cmdshell seems to cause a problem. What happens is the job starts and then never finishes. Additionally, the xp_cmdshell just goes forever and eventually has to be stopped by shutting down the server. I believe that it is getting to ExifTool because it actually creates the output file at the location, but it doesn't ever write anything to the file and never stops or fails. I further suspect xp_cmdshell because in one of my other iterations of troubleshooting, I tried to just just create a SQL Agent job that ran the command directly using xp_cmdshell without using the SSIS package and it had the same issue.

Anyway, sorry, that was a lot. Are there any known issues with running the ExifTool via a method like this? Any suggestions on how to get this working?

Thanks a ton in advance!!!

[Phil Harvey]

Sorry, I have no experience running ExifTool like this, but have a question:  You seem to be running exiftool.pl directly from cmd.exe.  This will not work.  exiftool.pl must be run via perl.  Or you can run exiftool.exe directly from cmd.exe.

Assuming that this isn't somehow the only problem, you should be able to test your technique by substituting something very simple in place of exiftool.  Maybe start with just an "echo" command then work from there.  Note that ExifTool needs permission to read/write a temporary directory, so you may want to make sure the exiftool process has sufficient privileges.

- Phil

[Fellhouse]

Hi Phil! Thanks for responding.

When I read through your response the first time, my first take was that the way I was invoking the command shouldn't be an issue as I have the .pl associations setup. When I thought about it a bit more, there is definitely a possibility that those associations are user specific... The thing I keep coming back to is that it actually creates the file and then gets hung up. I really think that xp_cmdshell is causing some sort of issue.

I will keep playing around with it a bit more.