Executing a whole container in one transaction

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

does the dataflow task get "stuck" if you execute it by itself?

Note that some operation in dataflow - caching for lookup - may take a long time - so how long does it stay stuck ?
|||

Liran R wrote:

Hello all,

I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.

To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".

The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.

Any idea?

Thanks,

Liran

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

|||Liran,

Is the behaviour same when you set the TransactionOption to "Supported" or "Not Supported", i.e. when not using DTC?

Also, take a look at the data flow window for that DFT to see which tasks are in yellow.|||

Jamie Thomson wrote:

You'll probably find that some operations are blocking others.

Are you using SQL Server? If so, execute sp_who2 and look in the Blk column for SPID=-2.

-Jamie

Yes, there is indeed a blocking:

SPID: 67

Status: SUSPENDED

Login: liran

HostName: Liran-Computer

DBName: STG

Command: select * from [dbo].[STG_FACT_A]

Wait type: LCK_M_IS

BlkBy: 64

SPID: 64

Status: Sleeping

Login: LinkAdmin

HostName: DW

DBName: master

Command: sys.sp_getschemalock;1

So I understand process 64 is locking the schema preventing process 67 from running.

Why is it happening? why only when using dataflow?

This behavior only happens when the container is set to "Required" in the transactionOption, and stuck on the dataflow (yellow).

Thanks,

Liran

Execute SQL Task

In SSIS Control Flow, I a have an Execute SQL Task that populates a result set with data.

How can I make that same data accessible to my data flow - so that I can export the data to a flat file?

Thank you

You will have to use a data flow after your Execute SQL Task to the best of my knowledge. In the data flow, you will have to use a data source to get the data I think.

Maybe some of the gurus here know of another way.

Execute Script component after 2 sequence finished with sucess

Dear Friends,

In the control flow, I have more than one sequence containers, and I have a script component that I want to be executed only when of 2 last sequence finished with sucess... these 2 sequences does not have any relation with each other...

Regards!

If you want the script task to be executed after both sequences complete successfully, add a precedence constraint connecting each sequence container directly to the script task. If you want the script task to be executed when either of the sequence containers complete successfully, do the same, but set the LogicalAnd property of the precedence constraints to False.|||

Dear jwelch,

I already tried it but didnt work...

Check the image on the image in my blog:

http://pedrocgd.blogspot.com/2007/06/ssis-temporary-image-to-msdn-forum.html

Thanks!

|||The dotted lines for the precedence constraints show that you have set it to be an OR condition. double click on one of those constraints and set it to AND which means both have to succeed before the next task is executed.|||

I made a mistake... I already had this works and I was confusing!!!

Thanks both!!

Execute phase Memory Error => dtsx finished: Canceled

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute phase Memory Error

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute parameterized select statement from data flow

I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.

Query is sort of

Select field1,fiel2... from table 1 where id = @.id

@.id is each ID from lookup

Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.

Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.

Hi Dnyandeo,

I had a similar problem when trying to implement a parameterized sql command. Firstly i used the execute SQL task.

It appears that you can only use named parameters with an ADO.Net connection. Using the OLEDB connection you have to use Ordinal position numbering.
I found this article which has some good stuff on parameterized SQL commands and helped me a lot.

http://www.juliankuiters.id.au/index.php?topic=dts-2005-integration

I hope this helps.

Cheers,

Grant|||how is your ole db transformation configured?|||

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

|||

dnyandeo wrote:

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

again, how is your ole db transformation configured?|||

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

|||

dnyandeo wrote:

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

it would difficult to assist you without knowing your exact t-sql code, you exact exec statement, and exactly how you're mapping the parameters.|||I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

|||

dnyandeo wrote:

I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

if i'm not mistaken, your sql statement in the ole db command should be the following:

exec CSP_GetJJKKChangedPricingFrequency ?

is this what you have as your sql statement?

Execute Package Utility

My SSIS package carries out one data flow task after another e.g.

truncate tables

Copy table 1

cleanup tables

When using the execute package utility in SSMS to run the package , the data flow tasks are listed alphabetically, so it reads:

cleanup tables

Copy table 1

truncate tables

Can I change this so the data flow tasks are listed in the logical order I have specified them in the package as it makes it difficult to work out which step the package is at. And while I'm at it, it keeps telling me every separate task's elapsed time is zero when it's finished. It records the start and end time ok, but it can't seem to work out the elapsed time?!

Thanks

I see your problem. Please go ahead and file a bug for the same.

http://connect.microsoft.com/SQLServer

We will take a look into this.

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.

Execute package results different to step by step execution - uses raw file

I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.

Here's the rub:

If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).

I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.

Any help is greatly appreciated.

I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)

Thanks

B.

Hi Brian,

Interesting.

A workaround question/suggestion: Would staging the data between tasks in a database staging table work for you?

Just curious,
Andy

|||

Hi Andy,

I was hoping someone has encountered this and has a quick fix - that said....

Short answer - yes - would probably work, but I would prefer not to (I don't really like using temporary tables if I can avoid them).

The data that is being stored in the files are new guids and their relationship to old composite ids, generated during the processing of the records. I guess that storing that information in a staging database is a possibility, but I would hate to have to re-work that entire block of code.

Ideally, if it can't be stored in raw files, then connection specific temporary tables would have been my next option - but those are not really available either.

Thanks,

B.

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....