SSIS Package Execution SQL Agent - Getting Wrong Results (Visual Studio 2019/SQL Server 2017/Windows Server 2019)

Question

Hi community,

I apologize if this issue is already solved in another forum...this is driving me nuts!

I am executing a SSIS package via a SQL Agent job and getting the wrong results. When I execute the package manually, in SQL Server, or in SSIS, I obtain the correct results.

I have tried to configure the package multiple ways including:

Using a proxy account to execute the SSIS Package via SQL Agent.

Changing to run-time to 32 bit.

The part of the SSIS Package Control Flow that is not working properly is an Execute Process Task where I execute a .bat file I created. The .bat file runs several command-line commands to "clean up" a .txt file to prepare it for BULK INSERT into a SQL Server table. I notice that when I manually execute the package, via SQL Server or in SSIS, the "clean up" process (the task) will produce a file with 145 rows. When I execute via SQL Agent, the file gets produced with 200 rows...

Please help.

Thank you!

-Noah

Answer 1

Hi @NoahWenger-2370 ,

If you run the bat file in SQL agent job, will it return the different result?

Will the txt file be affected by any other factors such as the file will change data with the time or other data?

Here is a similar thread you may take a carefully reference to see if it could give you some idea.

Also see this bat-file-works-manually-but-does-nothing-in-sql-server-agent-job.

why-does-my-ssis-package-produce-different-results-when-executed-from-a-maintenc

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.