October 15, 2009 at 9:41 am
I have a package which have multiple data tasks, here is the senario, on control flow tab I execute a sql task truncating 10 tables at a same time then inserting data with sequentiol order on data flow tab. If one data task fail to load data my whole package would fail, I need to avoid this, instead if one task failed it should skip that failed task and go to another task to load the data.
how I do it.
October 15, 2009 at 12:01 pm
Are each of the data inserts contained within their own data flow tasks? If so, ensure that the FailPackageOnFailure property on the data flow container is set to False. If the data inserts are all within the same data flow container, you can try manipulating the MaximumErrorCount property.
October 15, 2009 at 12:10 pm
yes each data load have its own data flow task, for your convinence I have 10 data flow task on data flow tab, on control tab I have one execute sql task which will truncate 10 tables at a same time.
October 15, 2009 at 12:35 pm
dave
Just to make u easier, i have one data flow task on control flow, and if you double click that data flow task, there are 10 tasks inside data flow tab.
October 15, 2009 at 12:45 pm
If all 10 data pumps are inside of the same Data Flow task, it will be an all or nothing load since everything inside of that Data Flow task will be treated as a single transaction. If you want to only fail the load with the error, you'll need to split each data pump into its own Data Flow task.
October 15, 2009 at 12:48 pm
Is there any way we can create a container and would be able to do with a single data flow task,
October 15, 2009 at 12:51 pm
Nope. In your case, the Data Flow task is the lowest grain container available to you. You'll notice that while inside the Data Flow task, there are no container objects available in the BIDS toolbox. Object containers are only available at the Control Flow level so all transaction behavior handling needs to be incorporated at that level.
October 15, 2009 at 12:57 pm
ya i mean to create a container inside control flow, in control flow tab, execute sql task is connected with data flow task, so if we put those two tasks inside a container, will it work?
October 15, 2009 at 1:01 pm
Each item inside a container will be part of the same transaction. If you want to allow one table's data to load if another table's load fails, they need to be inside a different container in the Control Flow.
So you need a seperate Data Flow task for each table load. This should be as simple as cuting-pasting the tasks into new Data Flows. You'll end up with 10 Data Flows, one for each table.
October 15, 2009 at 1:03 pm
That, or setting the Error options in each of your Destination objects to ignore the error or redirect the error rows to another table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply