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 Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute permission cannot be acquired?

I have an assembly which uses file I/O to stream in some text based reports
into a container in a report. I have set the FileIOPermissionAttribute and
it works in the design environment but when I deploy the dll to the report
server it only works on that box. If I try to run it through the preview
window I get the following error: "Execute permission cannot be acquired"
Anybody help?Did you get this resolved? Assuming that the security policies are updated
correctly and you assert the FileIOPermission in the custom assembly, it
could be a file system permission issue. Basically, the executing user does
not have permissions to access the file. Check the NTFS permissions on the
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:3FA6A470-1E7D-421D-AF3E-226ADAC397D9@.microsoft.com...
> I have an assembly which uses file I/O to stream in some text based
reports
> into a container in a report. I have set the FileIOPermissionAttribute
and
> it works in the design environment but when I deploy the dll to the report
> server it only works on that box. If I try to run it through the preview
> window I get the following error: "Execute permission cannot be acquired"
> Anybody help?
>

Execute Package Task behavior

I’m using a For Loop container to with an Execute Package Task inside, looping until a folder is empty.I’ve noticed some strange behaviors:

1. The child package keeps creating new connections.I start with 3 connections to the DB and when the For Loop container is done I’ve got 364 connections.

2. The Execute Package Task is pulling the wrong version of the package I’ve specified.I’m using a package saved to the File System and there’s only one copy on the drive.I’ve verified the path is going to the correct location.

Does anyone have a work-around for the ‘connection generation’ issue?

TIA

Eric

In your first question, do you load child packages from SQL Server? If yes - make sure you have connection pooling OFF.

Execute package on a for each loop container

Hi. I am running a package using 'execute package' inside a for each loop file iteration. I have to iterate through some 10,000s of files to ETL source data, a resonably complex package. I want to set going and get some sleep but every 200 files or so I get a memory exhausted error and the task fails. Is there a short easy fix to refresh memory each iteration. If so please let me know. Just ignoring the failure at task level won't refresh the memory, will it?

Thanks for the help.

Canyou try setting the ExecutePackage task to have 'OutOfProcess=True' - I am not sure if that will work but it is worth trying.