SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

James Alexande

SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

by James Alexande » Sat, 28 Apr 2012 23:27:09

I'm at a bit of a loss. I've written a package that has about a dozen data flow tasks and each one loads data from text files into tables in a db. It's possible that some of the fields in the text files may have data that would need to be truncated upon insert into the db.

In the data flows, I've set the fields in the data sources I know this could happen to to ignore truncation errors. When I run this within BI Studio, everything works fine. I then loaded the package into the SSIS store on the db server and if I attempt to run it from the SSIS store or if a job executes it, the package appears to be failing because of these truncation errors that I've told it to ignore.

Am I missing something Thanks!




Anthony Marti

SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

by Anthony Marti » Tue, 01 May 2012 00:28:10

Most likely the service that the job is executing under does not have access to the directory where the text files are located.



Anthony Marti

SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

by Anthony Marti » Wed, 02 May 2012 02:30:12

What is the exact error message you are receiving

When importing the package to the package store, what protection level did you use




James Alexande

SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

by James Alexande » Thu, 03 May 2012 01:29:11

I've ruled that out by giving everyone access to the directory. SQL Server, SS IS, and SS Agent are all running as Network Service.

Besides, when I attempt to execute the package from the SSIS service, it shows me which data flows it's failing on and they are the ones that I instructed to ignore truncation failures.



Rafael Sala

SSIS Package Failing When Run As A Job But Runs Fine In BI Studio

by Rafael Sala » Fri, 04 May 2012 03:31:13


How do you know that failure is due to the truncation It good if you can provide with the error message you are getting.

Here is an article with the mos common problems when executing a package via Agent Job:

http://support.microsoft.com/kb/918760