How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I get started talking to my webmatrix sql server

get a prompt up (in a program like telnet / msdos window) so that I can run sql commands on my webmatrix accounts sql server. I've been trying to connect using telnet but i think maybe I've got the wrong end of the stick or I'm doing something wrong.

here's a link where you click through "how to connect" on the "myserver page" I searched the page for "connect" and found 0 occurances.

http://europe.webmatrixhosting.net/text.aspx?tmpl=qa#3.7anyone please help,

the only way I know of to create table is through an sql prompt,

is there another way or can somebody give me some pointers cos i'm well stuck.|||Will webmatrixhosting let you use Enterprise Manager and Query Analyzer?|||i have windows XP pro, it doesn't come with it does it?

this isn't my main priority at the moment though,

trying to get an msde installed however is :

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=447274

http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=181961&Page=1#183199

then I might actually be able to start coding the ASP.NET I'm required to produced (pretty much by the end of the week)|||Enterprise Manager and Query Analyzer are "free" tools provided with SQL Server 2k.
Even when demo period is over, you can still use 'em.

Install MSDE.
Download SQL Server 2k.
Then just choose "SQL Server Tools only" during install.|||wicked!

I was looking for a trial on the ms site but couldn't find one b4 - didn't think they had one, downloading it now :)