SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS oddness


SSIS oddness

Author
Message
Michael Gerholdt
Michael Gerholdt
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 240
I have a package that consists of three sequence containers with success connectors. Each holds a number of tasks.

For debugging and testing, I disabled every taskbut one in the first container. This is an SQL task that connects to an SQL server and truncates a table in preparation for the next task, which is a dataflow task to put new data into the table.

When I debug, the container and the task both go yellow and then red. The progress report says that there was a problem making a connection to the database.

After much testing to no avail, I just right-clicked on the task itself and did an "execute task." Now it works fine. I have exactly the same experience with another task within the same container, this one connecting to an Oracle database. If I debug and let the sequence container in on the action, both sql tasks fail to connect. If I just execute each task individually, each works as expected.

So instead of hitting F5 and watching about 18 tasks fire off in proper sequence, I executed each individually with success.

What would be the reason that the task cannot run when fired by the sequence container, but it does run fine when executed directly?

SSIS 2008 R2 Standard Edition ...

Thanks for any ideas regarding this!

Mike
Evil Kraig F
Evil Kraig F
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27685 Visits: 7660
Containers can have some options that can mess with connections, like transactions, but it doesn't sound like you activated them. Go to the sequence containers and make sure they're set to TransactionOption Supported, and Isolation Level Serializable (that's the defaults).

Otherwise, I've never had similar issues to what you describe. I'd double check your Execution settings on the containers however, and make sure they're all set to False and MaximumErrorCount is 1. Once done, if you continue with problems, set delayValidation to True and see if that cures the issue.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
MyDoggieJessie
MyDoggieJessie
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15418 Visits: 7444
+1 @Craig regarding "set delayValidation to True"

You will find this setting on nearly every control and I believe on the package level itself

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Michael Gerholdt
Michael Gerholdt
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 240
Evil Kraig F (11/29/2012)
<snip>

TransactionOption Supported, and Isolation Level Serializable (that's the defaults).

</snip>


Somehow the TransactionOption had got set to "Required" on the first container, but not the others.

Setting it back to "Supported" provided the expected behavior.

Much appreciated!
Evil Kraig F
Evil Kraig F
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27685 Visits: 7660
Michael Gerholdt (11/30/2012)

Somehow the TransactionOption had got set to "Required" on the first container, but not the others.

Setting it back to "Supported" provided the expected behavior.

Much appreciated!


My pleasure. Sounds like a misclick somewhere along the way then, or something silly happened in the background. Glad that resolved it. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search