TransactionOption Property Not Working - Feedback Needed
Jun 12, 2006
I have a package which consists of 3 Execute SQL Tasks -
1) Drop old database & Restore a new one
2) Run DDL
3) Run DML
My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.
Is there something which I am not doing correctly?
How do I go about this?
Thanks,
Deepak.
View 1 Replies
ADVERTISEMENT
Nov 27, 2007
I have a Package and a DataFlow Task.
The Package has TransactionOption=Required.
The DataFlow Task has an OLE DB Source and an OLE DB Destination.
The DataFlow Task has TransactionOption=Supported.
The package executes on a Workstation and DataSources for the OLE DB Source and the OLE DB Destination are on a Server.
After the package had been launched an error message showed:
[OLE DB Destination [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DWH_Destination" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "OLE DB Destination" (43) failed the pre-execute phase and returned error code 0xC020801C.
[Connection manager "DWH_Destination"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
[Connection manager "DWH_Destination"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D024.
If I set TransactionOption=NotSupported in the DataFlow Task then the package executes successful.
What is the problem?
Thanks in advance.
View 4 Replies
View Related
Oct 3, 2007
Hi!
I have an idea to generate an animation file in SSIS based on the data it imports.
Has anyone done that? Is Flash the best format for this or something else? I have programmed in several languages, and written programs before that wrote other programs, but I have never used any of the animation tools such as Flash. Can anyone point me to some resources I can use to quickly get up to speed on how to write such animations, in whatever tool you'd recommend? The tool needs to be low footprint as the application I have in mind would go on Internet for customers to see.
Any feedback on this idea whatsoever is most welcome indeed.
Brgds
Danny
View 1 Replies
View Related
Jun 14, 2007
This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.
There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.
So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.
View 3 Replies
View Related
Mar 10, 2006
Just a note to the MS guys...
I'm fully supportive of the product Feedback Center initiative and the subsequent withdrawal of sqlwish.
Problem is, if nobody ever gives us feedback on the things that we submit then it simply is a glorified version of sqlwish with no added value.
I apologise if there are plans afoot to address to offer feedback to the things we put up there but I (and others) have been freely submitting bugs and suggestions for a few months now without hearing anything back and I'm beginnign to wonder why we bother.
Even a simple "This is a good idea and will be considered for Katmai" or "This is a terrible idea now go and stick your head back in the sand" would be better than a cut and pasted response which is just about all I've seen so far.
Comments???
-Jamie
View 2 Replies
View Related
Dec 3, 2005
Hi all...I have a stored proc that works fine except I can't get thepaging property to work in ASP. It seems that because I'm building theSQL withing the SP is causing the problem. If I don't build the SQL asa string paging works. In my ASP page, the pagecount property returns-1 for some reason.Here's the proc:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER Procedure LS_DisplayAllCategoriesBoolean@aSections varchar(255),@field varchar(255)Asdeclare @sql varchar(1000)SET NOCOUNT ONset @sql = 'SELECT DISTINCT ls_product_catalog.id,ls_product_catalog.ItemNumber, ls_product_catalog.Name,ls_product_catalog.[Descriptor], ls_product_catalog.Price,ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,ls_product_catalog.Sale_DescFROM dbo.ls_brand INNER JOINdbo.ls_product_catalog ON dbo.ls_brand.nID =dbo.ls_product_catalog.CategoryID INNER JOINdbo.ls_product_category ON dbo.ls_product_catalog.id =dbo.ls_product_category.nProductIDWHERE dbo.ls_brand.bVisible = 1 anddbo.ls_product_category.nCategoryID in (Select IntValuefrom dbo.CsvToInt(''' + @aSections + ''') ) 'if @field = 'bNew'beginset @sql = @sql + ' AND bNew = 1 AND(dbo.ls_product_catalog.bGiftOnly is null ordbo.ls_product_catalog.bGiftOnly = 0)'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGift'beginset @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGiftOnly'beginset @sql = @sql + ' AND bGiftOnly= 1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'sale_price'beginset @sql = @sql + ' AND (sale_price is not null ORsale_desc is notnull)'set @sql = @sql + ' order by ls_product_catalog.Name'endexec(@sql)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ON
View 1 Replies
View Related
Apr 30, 2007
Hi,
I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.
When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.
Anyone else experienced this problem and, if so, is there a workaround?
Thanks in advance
View 1 Replies
View Related
Apr 30, 2007
Hi,
I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.
When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.
Anyone else experienced this problem and, if so, is there a workaround?
View 3 Replies
View Related
Jan 31, 2011
Currently i am working on SSRS 2008 R2.The issue is that it is wrapping long words and not growing. I set the property can grow to True.
How to prevent the word wrapping?
For example, the column will have the word "information" in it. Instead of the column showing:
information
it shows:
informatio
n
The "n" gets wrapped to the next line. Is there a way to prevent this from happening.
How to prevent the word wrapping?
View 4 Replies
View Related
Aug 14, 2007
Hi,
I have a report which has 3 tables kept inside rectangle.I have PageBreakAtBegin Set to True for the last 2 tables.Hide Property for rectangle is False.Everything is working fine.
Moment,I place condition in Hide Property,no matter it's True or False,page break stops wroking.I can see all the tables in the single Excel file or report
Any help will be appriciated
-Thanks,
Digs
View 5 Replies
View Related
Jul 21, 2004
Hi
if i try to delete data via MFC from Microsofts SQL Server 2000
i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
please help its urgent.
Greets
Indian
View 1 Replies
View Related
Nov 20, 2006
Hi
I set the transactionoption = required at package level. This package has a couple of dataflow taks and both the tasks are set transactionoption = supported. If one of them fails, both the tasks should be rolled back. When I run the package, it simply hangs at the first dataflow task with no error message. I had to stop debug to stop the execution of the package.
My system has DTC service enabled with Network DTC access enabled and the sql server is running on my local machine, which is windows xp box.
Any idea why this happens and the solution or workaround for this?
Thanks
Ramani
View 5 Replies
View Related
Apr 18, 2006
Hi,
I was trying to maintain transactions between my tasks, so I set the TransactionOption property as Required to the Parent Container (For Each Loop Container). But I am getting the following error while executing my SSIS package.
"Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running."
I checked in my Windows Services and did not find MSDTC. Pls let me know if you have any solution for this.
Thanks in advance.
Regards,
Prakash Srinivasan.
View 5 Replies
View Related
Apr 12, 2006
I want to use transactions to protect my tasks. I have ten tasks that need to be completed in a single package. If any of the tasks fail - I need the entire process to rollback.
I have 5 execute sql tasks to truncate groups of tables in my destination database.
After each of the single truncate tasks - I use data flow tasks to copy the data from the source to the destination db.
Both dbs are on the same server.
I am basically copying the entire db - with the exception of a few tables.
package.transactionOption = required ,
and all tasks transactionOptions are set to supported.
Several questions. It seems to hang on the first data flow task - the output window stops, etc.
If I set package.transactionOption = supported - The package will execute.
If I create an error on the thrid truncate task - all previous tasks will complete - the package will fail on step 3 and the truncation for this step is not commited.
I am partially there. Can anyone help point me in the right direction? I need the entire process to commit if all tasks are successful or rollback if any fail.
thank-you in advance
Kim
View 1 Replies
View Related
Jun 7, 2007
I have a package that has three control flow items - an Execute SQL task, a Data Flow task and another Execute SQL task - linked in sequence by On Success constraints and contained by a Sequence container. I want any of the three contained tasks that fails to cause the Seuqence container to be rolled back and write a checkpoint.
I've configured the package to use Checkpoints, set the TransactionOption to Required and the FailPackageOnFailure to True ion the Sequence container and set the TransactionOption to Supported and the FailParentOnFailure to True on each of the three contained tasks.
When I attempt to run the package in debug mode, the first Execute SQL task executes fine but validation of the Data Flow task never finishes. If I switch the TransactionOption of the Sequence container to Supported instead of Required, the package executes fine.
I have had problems in the past where MSDTC was not running but that causes an error and I've checked that it's running and it is.
Any suggestions gratefully received...
View 3 Replies
View Related
Apr 3, 2008
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
Thanks!
Matt
View 4 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
Apr 17, 2008
Hi,
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Thanks for any hints.
View 3 Replies
View Related
May 7, 2008
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
View 10 Replies
View Related
Apr 19, 2007
Dear friends,
I'm thinking to take this exams soon... anyone has documents, exams, links or other to help me on it?
Thanks!!
View 7 Replies
View Related
Dec 9, 2002
Might be purchasing the SQL Spotlight product, any experience/input from current users would be appreciated. Looks like good product, little worried about the overhead
View 3 Replies
View Related
Jan 19, 2006
I haven't touched MSSS in a looong time
When a SELECT returns say 3 rows what does the feedback say ?
(eg Oracle/sqlplus says "3 rows selected")
What if "no data found", what is the feedback ?
thanks
View 5 Replies
View Related
Dec 8, 2006
hi. ive just written my first complex SP for SQLServer 2005. id be REALLY grateful for any feedback please. if there's anything i'm doing wrong then it's better that i know sooner rather than later! im wondering if its over complex or just plain wrong. please bear in mind that i have at least made an attempt at this, this is my first proper SP.
the SP that I have written is to insert an Account and a User. The SP should do the following.
1. Start a transaction.
2. Insert a user by calling a nested SP. if the return value is greater than zero then the user has been successfully inserted.
3. only attempt to insert an account afterwards if the user has been inserted successfully.
4. if the user or account insert is not successful then rollback. otherwise commit the transaction
5. return an error code from the SP as such: 0 = success. 1 = failure, a user already exists with the given username. 2 = an unknown error occurred whilst inserting the user. 3 = an unknown error occurred when inserting the Account.
here is my code:
CREATE PROCEDURE sp_Account_I
@Id_Package smallint,
@Amount money,
@Username varchar(16),
@Password varchar(88),
@FirstName varchar(32),
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64),
@Id_Country int,
@Id_Account int OUTPUT,
@Id_User int OUTPUT
AS
DECLARE @ErrorCode int;
BEGIN
SET NOCOUNT ON;
SELECT @Id_Account = -1;
BEGIN TRANSACTION
/* Insert the user. */
EXEC @ErrorCode = sp_User_I1
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country,
@Id_User OUTPUT
IF @ErrorCode > 0
BEGIN TRY
INSERT INTO Accounts(
Id_Package,
Id_UserMain,
Amount)
VALUES(
@Id_Package,
@Id_User,
@Amount)
SELECT @Id_Account = SCOPE_IDENTITY();
END TRY
BEGIN CATCH
SELECT @ErrorCode = 3; /* An unknown error occurred. */
END CATCH;
IF @ErrorCode = 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN @ErrorCode;
END
END
View 8 Replies
View Related
Jul 23, 2005
I have some long running scripts which I fire at my database using osql.(These are big files and mostly doing inserts but some also do a few otherthings.) It would be nice to have some activity indication (other than thedisk activity light) that these are running. When I used to use Oracle,their equivalent to osql had an option to print a dot (without a carriagereturn) for every "n" statements. This gave a nice "I'm alive" indicator. Ican simulate this by adding a few "print" statements in my sql, but printalways adds a carriage return. Does anyone know a way of doing a print butwithout the addition of a CR (or CR/LF)? So that a second "print" sends itsoutput to the same line as the first?I know this is a nicety and I can live without it, but it would be nice.thanks in advance,Brianwww.cryer.co.uk/brian
View 4 Replies
View Related
Jan 19, 2006
I haven't touched MSSS in a looong timeWhen a SELECT returns say 3 rows what does the feedback say ?(eg Oracle/sqlplus says "3 rows selected")What if "no data found", what is the feedback ?thanks
View 4 Replies
View Related
Apr 4, 2007
I€™d like everyone€™s help is do some research into an often heard, but rarely explained, complaint related the SQL Express. Your answers will help me plan future versions of SQL Express. Feel free to respond directly in the forum or by sending e-mail to me. (Note: Remove the word online from the e-mail address in my profile or it will bounce.)
The complaint: €œSQL Express is too big.€?
I€™m trying to understand what this really means and what specific technical issues are caused by this €œbigness€?. Here are some questions to help frame your answer.
Size means size
1. Is the size of the SQL Express installer package an issue for you? (SQL Express 32 €“ 52 MB / SQL Express Advanced ~250 MB)
2. Why is this size an issue?
3. Would you be willing to sacrifice functionality to reduce the size of the installer package?
Size means disk space
1. Do the SQL Express binaries take up too much room on the hard drive?
2. Would you be willing to sacrifice functionality to reduce the amount of HD space needed?
3. Do your databases take up too much disk space?
4. Would you be willing to pay money to reduce the size of the database file?
5. How much money?
Size means memory
1. Does SQL Express take up too much memory when it€™s running?
2. What impact does this have on you?
3. SQL Express currently reduces its memory set when it is idle which results in a delay when it becomes active again? Is this a reasonable trade-off to reduce memory usage when you€™re not using the database engine?
4. Do you normally use SQL Express for single user applications (local data store) or for multi-user applications (server data)?
5. If you run SQL Express as a server, do you run it on a dedicated computer or on a computer running other programs as well?
6. What kinds of programs does SQL Express have to share with?
7. Should SQL Express give up memory resources to other programs running on the same machine?
8. Are you willing to accept a reduction in performance in order to have memory resources shared?
Size means something else
1. Is there something I didn€™t cover?
I€™ll be tracking this thread, but will try not to comment to much since this is about your feedback, not my answers.
Mike Wachal
SQL Express Program Management
View 35 Replies
View Related
May 19, 2008
I wrote a post in the connect forums almost two weeks ago, but haven't got any response yet.
So I'll post here as well, hoping that someone from the SQL team will stumble upon this.
My post is about a possible bug in SQL.
Here's the url:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342390
Marko
View 3 Replies
View Related
Jan 6, 2008
hi all
iwant to make feedback after insert data or delet date Like( the data is sucssfuly way) or(data is delete sucssuse way)
but not alert iwant feedback
thank you
View 4 Replies
View Related
Jan 18, 1999
I am interested in hearing from those who are using SQL7 in production. Please include size of database, number of users, implementation date and experiences to date - good bad or indifferent. Thank-you, Leo
View 1 Replies
View Related
Jan 12, 2004
When I call osql -S server -E -h-1 with a -i option, the feedback I get is prefaced with "1> 2> 3>".
The test script is simply:
set nocount on
use master
select name from sysdatabases
How can I suppress the "1> 2> 3>"?
Thanks
View 2 Replies
View Related
Apr 11, 2008
Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
View 4 Replies
View Related
Jul 16, 2007
[Microsoft follow-up]
I submitted a posting to connect here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287213
Here is what I wrote:
A number of tasks output data to variables or text files. e.g. WMI Data Reader Task, Execute SQL Task, Web Service Task.
Consuming that output in a data-flow isn't particularly easy. You either need a Flat File source adapter (which requires an othrewise superfluous connection manager) or write come code in a script component to parse the recordset. There is simply no easy way to push data from these sources into the dataflow.
Thw built-in mechanism for passing data between different tasks is raw files. Currently they can only be used by the dataflow but I see no reason why they couldn't be used by other tasks as well. It makes complete sense to me for a WMI Datareader Task to push some data into a raw file and then we chew that data up in a dataflow.
The following response came back
Our current architecture actually doesn't have the buffer system as in Data Flow, when you are in the Control Flow. What you are asking would require us to build a similar buffer system in the Control Flow, which is a fundemantal architectural change. We'll not be able to take this, sorry.
I'm afraid I don't understand that response. Obviously I know that buffers are only in the data-flow - but I don't see why that's relevant. Raw files are just files on the file system, same as any other. OK, their format is very proprietary but its you guys that built the format. Essentially all I'm asking you to do is output the data in raw file format as opposed to flat file format. There's no notion of buffers in a raw file because its just a lump of data. Or is there? If not, I'm afraid I don't understand your answer.
Please could you clarify the answer?
-Jamie
View 14 Replies
View Related
