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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s