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 SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I figure out where SSIS logs are?

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

Open them in a text editor. I'm a bag fan of Textpad (www.textpad.com)

You control where they are. You tell tell the package where to log to.

-Jamie

|||

Hi Jamie,

Come on man, textpad? Is my favourite without a doubt, I like it a lot too.

I was saying, in what path ssis leave its logs. where?

Thanks again

|||

Like I said previous, the logs are put wherever you tell SSIS to put them. That can be a text file (it is you, not SSIS, that defines where that file resides), SQL Server (in which case they'll be in the msdn..sysdtspackageslog90 table if memory serves), event log, SQL Profiler or an XML file.

-Jamie

How do I execute a stored Procedure using SSIS

Please anyone help me with my question

How do I execute a stored Procedure using SSIS ?

I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005

Thanks,

George

It's been awhile, but don't you just type in this into the sql statement slot:

exec my_procedure_name_here

|||

Thanks for your help

How do I do thisexec my_procedure_name_here

What I have to do in SSIS the steps..please

Thanks again

George

How do I edit an Imported DTS package in SSIS?

I have used the wizard to import a DTS package from a SQL 2000 server to our new SQL 2005 server and need to make edits to reference the new server and database. I am able to see the package within the SQL Management studio under Integration Services, but I cannot find it in the Development studio?

What am I missing?

Thanks

The development studio is an offline tool that works on file packages only. Export the package from SQL Server / IS and add it to a project in BIDS.

How do I do "If statement" in SSIS?

I need to get value from one table and if it is (for example) string - TodayDate, I need to change it to "Today Date" with a space in it and use it later in my Data Flow.

So I would need something similar to

If value = "TodayDate" Then

value - "Today Date"

Else

.....

How do I do that?

Thanks.

Check on the Derived Column transform in SSIS dataflow task.

|||

To add to Wenyang's answer...you will need to use the Conditonal Operator within the Derived Column transform.

? : (Conditional) (SSIS)

(http://msdn2.microsoft.com/en-us/library/ms141680.aspx)

Wenyang, I don't thinkyour answer is complete. Do you really think it warrants being marked as an answer? Hope you don't mind me asking.

Regards

Jamie

|||

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

|||

Vita wrote:

How do I create expresiion?

Column Name - MonitorType

MonitorType ? "SA" : @.MyVariable

This shows error.

I want to see if the MonitorType field returns "SA" and if yes - use the variable I have.

If it returns for example "BA" use another variable.

MonitorType == "SA" ? @.[MyVariable] : @.[SomeOtherVariable]

-Jamie

|||

Thanks.

|||Please don't forget to mark posts as answered.|||

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

|||

I believe you can continue to add additional tests in the Derived Column task.

Rob

|||

Vita wrote:

What if I have more than 2 variables.

If "SA" - var 1,

If BA" - var 2

If "DA" - var 3

If "DS" - var 4

Can I do Else If in the same expression?

Yes. You need to nest the Conditonal Operator.

MonitorType == "SA" ? @.var1 : (MonitorType == "BA" ? @.var2 : (MonitorType == "DA" ? @.var3 : (MonitorType == "DS" ? @.var4 : @.SomeDefaultVariable)))

-Jamie

please don't forget to mark as answered.

|||Thanks again. I appreciate it.