SSIS – File path is not valid – job failed on reading second file path

Possibly save 1 hour of your time: We have a SQL Server agent job that runs multiple SSIS packages. Each package is independent. Each package will process a data file that is located on another server. The data files for all packages are stored at same location. When the job runs, it always failed on the second package regardless of which data file it is looking at. So if step 1 ran, then step 2 will failed. The next time you run it, Step 2 will passed and Step 3 will failed on reading the file. The error will say the file path to the file is not valid.

Funny enough, when we give full administrator right to the identity running the job, all the steps passed.

We start thinking this was a permission issue so we check all the database permissions for this identity and could not find the issue.

Solution: We found out that there was a mapped share drive created. This was causing the issue. We use UNC path as environment variable to find the file. However the mapped share drive has the same beginning path like \\server\folder. Once we removed the mapped share drive, the job is reading subsequent file properly and behaving as expected.

Advertisements

SSIS – Could not find stored procedure sp_ssis_addlogentry

Possibly save 1 hour of your time: For some reason when the SSIS package was deployed in a different environment and when it ran, it gives this error.

The stored procedure is not automatically created.

Solution: In my case, the issue was actually related to the SQL Server Agent job not setup probably to tie to the environment variables. For some reason that causes this issue.

After the job steps have the environment variables linked, then job is running fine and the stored procedure was automatically created.