Ok, I have my SSIS job that is using Execute Process task, this task is calling a batch file that FTP's files over from the server to a new location for processing. The package works locally in SMS, no problem at all. When I export the package out to my SQL Server under integration services, it runs but I get a pop up message; 'Open File - Security Warning The publisher could not be verified. Are you sure you want to run this software? Name: remove.bat Publisher: unknown publisher type: windows batch file from: \\servername\\Files\ when I click run, its runs, but now, when I put this in SQL Job agent, I get no pop message and it doesn't execute. Now I don't get any pop up when I run the package in SMS. What do I need to do to get this package to run under SQL Job agent so no pop message is there?

What account are you running your SQL Agent under? If it's a domain account, did you add it to the SQLServerAgent local group? Try adding the user to the local admin group, and see if that clears up the problem. (Change it to a different group with the right privileges afterwards.)

There's nothing in the event viewer for errors, and as for ID, I gave it an ID with 'GOD' access and still no luck. Any other suggestions on what it may be?

I know SSIS (any anything .NET, really) can be funny when dealing with network resources like your filepath \\servername\\Filename. Have you tried bringing the batch file to a local drive and executing it there? C:\temp or other. BTW, should there be two back-slashes after your servername...?

I can run the package from my local machine in SMS, I can also run the .bat file by clicking on it, also I can run it when I do a right click --> run package on the Server itself. Its when I try to run it under SQL Job agent.

On the actual SQL server no. The job stops at the step that is calling the batch file that is doing the FTP work.

It sounds like your SQL Agent user may not have execute permission. Try giving it explicit execute permission on the drive where the file is located. I think you should consider trying to bring the batch file to the local server and reference it locally instead of via UNC... This might narrow your problem down to permissions across the network...

The batch file is on the same server as the SSIS is on. So its "local" its just stored on another drive instead of the c:\ drive. The ID that I'm using has full access and runs other jobs under SQL agent fine, its running every job fine, except when it gets to the FTP portion it just clocks and never does anything, no failure, etc.

Have you tried log into the server using the SQL Agent account and run the bat file and try to reproduce this error message?This seems like it's waiting on a user input on the command prompt.

I got it working finally. What I had to do is create the SSIS package right on the SQL server and physically point the Execute Process Task to the batch file, instead of using \\computername\foldername\batchfile even though it was on the same server as the SSIS package was running. Now, I have an issue with this because my servers don't have SMS to create packages in, but this was the only way I was able to get it to work.

Sorry, I should have been more clear when I said try pointing it to a local path like C:\temp... What you did is exactly what I was trying to suggest. What I do is mirror the server files and locations on my local pc when I build the packages. That way the tasks can validate and so on. Then, when they are deployed, everything is in order. It's sort of a hack way of doing things, but SSIS does not really provide a lot of other ways to do it. You can, of course, turn off task validation while developing. But this only works with some tasks. Flat file connections, for example, require a file to exist before it can besuccessfully added to your project... Glad you got it worked out!

For my future SSIS package development, do I have to use the actual path to run this BAT files? Are you not able to use a UNC path to run files such as .cmd, sql, or bat files?

CSharpCoder wrote: For my future SSIS package development, do I have to use the actual path to run this BAT files? Are you not able to use a UNC path to run files such as .cmd, sql, or bat files? You can use UNC, but just make sure that the account that the SQL Agent service runs under has access to that share. You cannot use mapped drives though, you must use UNC (\\server\share\....)

The ID that is being used for this job and SQL Agent has full access to the UNC path. This job is also deleting files in the same location tha the bat file is located at. So if I have \\servername\folder\ its deleting all of the files in that location, but as for running a bat file in that same location, no luck unless I have c:\folder\ftp.bat and ID has full access to the folder, and if I run this job locally (from my computer) and not in SQL Job agent it runs fine using the UNC path. I also tried to run this in my local SQL version and point it to my local pc such as \\mypc\folder\ftp.bat and it failed, but if I point it to c:\folder\ftp.bat, it runs fine, and again, the SQL Agent Job ID as full access to that folder. Could it be something esle that I could be missing, a serivce pack, update, etc, anything?

How are you executing the batch file? Execute Process task? You'd need to execute CMD.EXE and pass the batch file in as an argument to the /C flag, I believe.

I'm running it through Execute Process Task

I have tried your solution. Still am facing the same issue.Am unable to execute this batch file using sql server job.Regards,Venkatesan Prabu .JMicrosoft MVPraj

Ok, I have my SSIS job that is using Execute Process task, this task is calling a batch file that FTP's files over from the server to a new location for processing. The package works locally in SMS, no problem at all. When I export the package out to my SQL Server under integration services, it runs but I get a pop up message; 'Open File - Security Warning The publisher could not be verified. Are you sure you want to run this software? Name: remove.bat Publisher: unknown publisher type: windows batch file from: \\servername\\Files\ when I click run, its runs, but now, when I put this in SQL Job agent, I get no pop message and it doesn't execute. Now I don't get any pop up when I run the package in SMS. What do I need to do to get this package to run under SQL Job agent so no pop message is there? This is a windows security error not the SSIS setup issue. When you run a batch file from UNC path, OR do an operation in a batch file which executes an operation involving UNC path you will have this security error. To enable UNC access you have to go to your network settings and on the security tab select "Local intranet" and click Sites button and check "Include all network paths (UNCs)". Alternatively you can add the IP of the FTP site that you want to access in list of trusted sites. Hope this helps- Please mark the post as answered if it answers your question

Did you tried using the Execution Option with check "use 32 bit runtime" in the SQL agent. Regards, Lucky