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

NoahWenger-2370 6 Reputation points
2022-02-07T14:19:35.837+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,612 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 32,411 Reputation points
    2022-02-08T07:54:09.647+00:00

    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.