Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







SSIS Package Transaction Integrity


1) I created many data flow task packages.

2) Then a execute package task in which I ordered relationally execute above data flow packages.

3) What I find is when a package fails, it is rolled back, execution stops but not the packages before this failed one.

4) What to do to rollback all in case of error and commit only if all are successfully executed?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SSIS Package As One Transaction
Hi,
I am a newbie in SSIS. I'd like to hear your recommendation to build the whole SSIS package as one transaction. In other words, how we can rollback all DML operations (insert/update/delete) that are applied in different parts of a package if for any reason the package failed to complete?. Something like rolling back a proc if the proc failed.

Thanks in advance

View Replies !   View Related
How To Use Transaction In A SSIS Package
I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a désactivé la prise en charge des transactions à distance/par réseau.".

Can someone help ?
thanks

View Replies !   View Related
SSIS Package Transaction Limit?
I am running a package in which all tasks participate in a transaction.  One task is a ForEach Loop container that executes 1 SQL INSERT statement per loop.  The Loop runs well over 100 times in order to normalize a very, very, very wide table.

It seems that at a certain point, the Loop task will fail.  I've tested a few different scenarios and they all fail at the same point... the 98th loop.

The error message is below:

[Execute SQL Task] Error: Failed to acquire connection "ADO.NET Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.  

I'm guessing that there is a limit to the number of connections that can be started at any one time.  But I figured SSIS wouldn't start a new connection for every SQL command.

Any insight on this issue?  Anyone have an idea for a work around?

View Replies !   View Related
Can A SSIS Package Join An External Transaction?


Hi there,

I'm trying to come up with the best way to build some C# Unit tests for an SSIS package I've built.

My C# codedoes the following

1. Creates a Transaction Scope using System.Transactions

2. Puts some source data for my package into a table that the package will read

3. Kicks off the Package using System.Diagnostics.ProcessStartInfo

Im getting a Transaction TimeOut execption which I beleive is caused because the Package can't read the source data I've inserted becuase the package has not joined the transaction.

So the question is - can you call a run a package and make it participate in a transaction that you have created outside of the package?

Thanks.







View Replies !   View Related
Implementing Transaction In SSIS Package Using SQL Task


Hi all,

I am having a simple ssis package. In that i have an sql task that will insert a record in to the database. i find that if i use a simple OLEDB connection it works fine. but the problem comes here when i change the connection string with an valiable. Once i assign the same connection string to a valiable and assign the variable as expression, there after i encounter the following error.



[Execute SQL Task] Error: Failed to acquire connection "DB connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

I am struggling with this for a long time.

Any of you plz suggest me a solution for this problem

Thankz

View Replies !   View Related
SSIS Package In Transaction Required Mode?
Hi Guys,



Refering to my previous post I've a package which easily run when transaction mode is Supported but when i put it to Required and All DFs are supported inside the sequence container it won't run.

Well technically it goes and pass step 1, so it goes to green but regardless of what i put in the 2nd DF it won't run. It just hangs in there.
When i check the component service and check DTC, the transaction would be running but never commited.

I have done most of the things about DTC and configuration with the MS articles as this package was failing before but now it just sits at this point.

Any ideas?



Thank you



Gemma

View Replies !   View Related
Using Transaction On SSIS Package Failed On Cross Domain.
I want to use Transaction(MS DTC) in SSIS package across domain. It's working fine if both the servers are in Corpnet microsoft domain but failing it one of the server at extranet microsoft domain. I did all the required settings for MS DTC service to run for distrubuted transaction and in SSIS package did the "TransactionOption" property for the container object to "Required" and for all inner tasks to "Supported". Is it possible or a Security voilation which won't allow to do through SSIS package.



Example: I have a database on Extranet server and a database at Corpnet Server.

Sql Job will pull the data from Extranet Database to Corpnet database through SSIS package and update back to Extranet database. Job will reside on Corpnet server.



SSIS package is failing with the error message: The AcquireConnection method call to the connection manager "<Connection Manager name>" failed with error code 0xC0202009.



Any special setting do we require for this. I did the following setting on both the servers:

Ø MS DTC should run on both the servers under Network Service?
Ø Set the following on both the servers(ExtranetCoptnet) to run on a Distributed Transaction:
Go to "Administrative Tools > Component Services"
On the left navigation tree, go to "Component Services > Computers
My Computer" (you may need to double click and wait as some nodes
need time to expand)
Right click on "My Computer", select "Properties"
Select "MSDTC" tab
Click "Security Configuration"
Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP"
The service will restart



Note: If I will chage the Extranet server to Corpnet then it's working file for me.

View Replies !   View Related
Check Database Integrity And Transaction Log

Our system is 24/7, ldf file disk space is 30 gb.

As per the advice given by somebody
i set ldf initial log size as 29 gb and restricted the growth and set the autogrowth to 500 mb
i did not run and shrink ldf command on daily basis. this is the advice given by another dba.

then he suggested to create a maintenance plan to check database integrity check. whick will
check the disk space threshold.

i did not understand how will the maintenance plan check the disk space and give an alert.
where should i check the alert for this maintenance plan. secondly how can i stop my database log file to deliver a message that my disk is full.

please suggest me a best method. where i am wrong , how should i handle the situation without the dba monitoring.
and our system has lot of batches running for every minute.




View Replies !   View Related
Referential Integrity - Linking Multiple Tables To Transaction Table
I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe rest of the tables. When I make changes to the values in Table 1 -4, I need to be able to reflect this in the Transaction table.Thanks.

View Replies !   View Related
SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
I have a design a SSIS Package for ETL Process. In my package i have to read the data fromthe tables and then insert into the another table of same structure.

for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.

I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.

if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

and

Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.

Please help me it's very urgent.

View Replies !   View Related
Feedback From A SSIS Check Database Integrity Task
Hi All,



I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity)in as the first task and if the Database checks out ok then continue on, if not send an email.

Now, I am unsure if the Check Database Integrity Task in SSIS actually returnsthe success or failure message back to the package that I am after. The SQL behind the taskincludes the NO_INFOMSGS option.

Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result?



Regards

View Replies !   View Related
Implementing Transaction In SSIS Package - [Execute SQL Task] Error: Failed To Acquire Connection &&"&&<ConnectionName&&>&&".
I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server.

When I execute this package It works fine. So far so good.

Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error.

[Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection.

"NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server.

Please respond if anyone has any solution.

Thanks!

Anand

View Replies !   View Related
Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

View Replies !   View Related
TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.&&"
I'm receiving thebelow error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View Replies !   View Related
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

View Replies !   View Related
Problem With SSIS Transaction...Transaction Scope


Hi,

I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

. My workflow is as followed

*********************************
For Each ADO.Record in Oracle (transaction=not supported)

If(Certain_Field_Value = 'A')


Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence A (Start a Transaction , transaction=required)


INSERT/UPDATE some records in SQLDB(transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'B')


Lookup Data in SQL DB with values from Oracle (transaction=not supported)

DO Sequence B (Start a Transaction , transaction = required)


INSERT/UPDATE some records in SQLDB (transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'C')

------------
------------
End ForEach Loop
*************************************
My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.
But I am getting an error regarding the OLEDB Error in next Task (e.gin Certain_Field_Value = 'B')"Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".
What is it that I am doing wrong?
Regards
KyawAM

View Replies !   View Related
Error While Executing SSIS Package From Other SSIS Package
Hi,

In ourproject we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

View Replies !   View Related
Error Stating Package Failure While Executing SSIS Package In Standard Edition
Hi,

I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.

But when i try to execute the packahe in Sql Server 2005 Standard edition,by invoking the package through .Net console application the status of the package is failure.

Can any one help me how to over come this problem.





View Replies !   View Related
Passing Execute DTS Package Result (success/failure) To Calling SSIS Package
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as asuccess, we would prefer to know it went wrong.

As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.

Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?

If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?

View Replies !   View Related
Several Different Errors When Creating And Running SSIS Package. Package Runs Successfuly Sometimes.
Hi All,



I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.



Sometimes when I am creating a package I get the following error in a message box:



SQL Server Import and Export Wizard

The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.

Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)





Other times when I run a package that has run successfully before I get the following error:



Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.





Other times I get this error message:



.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)





And still other times



The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.





I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.





Does anyone have any other suggestions to try?





Thanks.





View Replies !   View Related
Is It Possible To Change Or Replace The Default Package Template Named New SSIS Package?
I would like to standardize SSIS development so that developers all start with the same basic template. I have set it up so it is an available template (http://support.microsoft.com/kb/908018 ) but I would like it to be the default when a new project or package is created. Is this an option?

View Replies !   View Related
Problem When Running A SSIS Master-package-child Package On Non Default Sql-server Instance
Hi there

We have a SSIS run which runs as follows


The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pcs.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.

The package is run from the file system, through a job-schedule.

We experience the following when running on a not default sql-server instance (called dkms5253uedw)

Case 1:
The master package starts by executing three sql-scripts (drop foreign keys, truncate tables, create foreign keys). This works fine.

The master package then executes the first child package. We then in the sysdtslog get:

Error - cannot connect to database xxx?
Info - package is preparing to get connection string from parent ?

The child package then executes OK, does all its work, and finish. Because there has been an error, the master package then stops with an error.

Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.

Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.

Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with


Error - timeout when connect to database xxx?
Info - package is preparing to get connection string from parent ?

And the continues as in the first case

From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).

We have tried delayed validation in the packages and in the connection managers, but with the same results (error).

So we are desperately hoping that someone can help us solve this problem.


Thanx,
/Nils M - Copenhagen

View Replies !   View Related
Passing Value From A Child Package To The Parent Package That Calls It In Ssis
hi,



I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.



I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.



I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...





Any suggestions?



Thanks for any help in advance..



smathew

View Replies !   View Related
How To Compose The Connection String Of A SSIS Package That Execute Another Package?
Dear All,

I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?

Regards,

Strike

View Replies !   View Related
Report With SSIS Package Having Indirect Package Configuration Setup
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.

The Report works when Direct package configuration is used to same dtsconfig file.

What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.



View Replies !   View Related
Problem Setting Package Variables In SSIS Package
Hi,

I am making use of the DtUtil tool to deploy my package to SQL Server.
Following is my configuration:
32-bit machine and 32-bit named instance of Yukon.

I have some package variables which need to be set in the code.

Previously I did it as follows:

Set the package variables in the code. For example:

pkgFile.Variables["User::DestinationServerName"].Value = <myvalue>

Deploy the package as follows:

applnObj.SaveToSqlServer(pkgFile, null,
destinationServer, null, null);

Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.


Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool.
Now I am doing it this way:

Set the package variables as before.
Deploy the package to SQL Server using DTUtil tool.

Now is the problem:
The package is successfully deployed. But the variables are not set to the value that I have specified in the code.

I also tried DTexec utility to set the package variable. Even that does n't work.
Can anyone help me out? Is there any alternate method to set package variables?

Thanks,
Sandhya

View Replies !   View Related
Schedule A SSIS Package Which Execute DTS 2000 Package


I have successfully created a SSIS package which execute a DTS 2000 package and with no problem to execute the task. But I failed to schedule this package. I was not success in setting the logging. When running the package in command line:







dtexec file "C:Documents and SettingslyangMy DocumentsVisual Studio 2005ProjectsTraingDTSTraingDTSDTSTraining.dtsx"


Error: 2008-03-24 08:03:24.36
Code: 0xC0012024
Source: Execute DTS 2000 Package Task
Description: The task "Execute DTS 2000 Package Task" cannot run on this edit
ion of Integration Services. It requires a higher level edition.
End Error
Warning: 2008-03-24 08:03:24.38

Code: 0x80019002
Source: DTSTraining
Description: The Execution method succeeded, but the number of errors raised
(2) reached the maximum allowed (1); resulting in failure. This occurs when the
number of errors reaches the number specified in MaximumErrorCount. Change the M
aximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).


Any help will be greatly appreciated.

(32 bit machine, standartd edition of SQL 2005)



View Replies !   View Related
SSIS Parent Package Can Not Find The Child Package
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.

View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

If you links or documentation which explain this scenario then it will help me to forward it to our DBAs and do the needful.

thanks a lot

$wapnil

View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

View Replies !   View Related
SSIS Called From Agent Job Not Calling New Child SSIS Package Addition
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.

I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.

I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages just not my new one.


Any thoughts why the agent will not run the child newly crated childe package?


View Replies !   View Related
SSIS Package Failing As A Job, But Getting Successful When Run As Package
when i run the job using network service account credentials job is failing. But when i run the package individually, it is tasting success. when it runs as job, this is the error message i am getting

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I have changed to 32-Bit run time and ran the excel package, even then it is failing...i tried to use my credentials (i am admin on the box), even then it is failing...please suggest

View Replies !   View Related
DTS: Transaction For A Whole Package
Hi
It's easy to make transactions from one workflow to the other. Is there a possibility to make a transaction over a whole package (with several tasks and transformations) ?

Thanks for statement
Mike Burger

View Replies !   View Related
SSIS - Get ServerName From A XML Or TXT File Into SSIS Package
Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!
Regards!!
Thanks.

View Replies !   View Related
SSIS - Tracking An SSIS Package Execution
I would like to see if someone can help me out.

Scenarion:
1.- SSIS Package execute taskson 2000 SQL Server Database
2.- Execution takes places using Business Intelligence Studio
Question:
1.- How can I tracked that SQl 2000 tasks took place using a SSIS Package?

Thanks

View Replies !   View Related
Package Transaction With OLE DB Transformation Transactions
Greetings,

Ihave arequirement from the client that specifies to rollback every insert/update that happenned in the package if any task (control or data flow) fails.

I'm certain the SSIS package-leveltransactions take care of this, however, in this package, there is an OLE DB Transformation that executes a stored procedure which has a transaction in itself.

so to draw a quick picture...
Package
{

Transaction1
{

Data Flow
{

OLE DB Transformation
{

Stored Procedure
{

Transaction2
{
}
}
}
}
}
}

Here's my question:
What would happen if an error occured in the stored procedure (Transaction2)?

Does it behave likeSQL Server 2005 where, given a scenario of nested transactions, the innermost transaction is comitted and the outermost transaction is rolled back?


I'm hoping that if the stored procedure decides to rollback Transaction 2 via error handling or if an SQL error occurs that I can rollback Transaction 2 and log an entry in the audit log.

View Replies !   View Related
Transaction Being Aborted During Package Execution
Hello,
I have a package with a single data flow from a remote SQL Server 2005 to a Informix 10 (OLE DB 3), and with transaction enabled.
If I run it inside BIDS, it finishes without reporting error, but Progress tab gives me a warning: Transaction has already been aborted.
Indeed, there's no data in the target table after package execution...
I have already set up network DTC in Windows Server 2003. Also, i have enabled DTC tracing, in case anyone find it useful.

Please, any suggestion will be valuable, as long as I can't see no solution for this. Thanks in advance.

View Replies !   View Related
REAL Project ETL Package Transaction Handling
Hello,
I am reading Microsoft REAL project ETL part.I find that it uses neither transaction nor checkpoints throughtout the packages. I am wondering if something goes wrong with the process and the process aborts in the middle,how are we going to handle this. Any particular reson not using transaction? Is it best practice without using transaction forperformance consideration and log volumn?I am new to SSIS. I would like to follow the best practice. Thanks for advice..


Regards

Jerry

View Replies !   View Related
Referential Integrity Constraints W/o Referential Integrity In The Db?
Using the new referential integrity constraints that will be made available, will it allow us to manually define the relationships between entities even if there is no true foreign key constraints setup in the database?

Lets say we deleted the FK_Orders_Customers in Northwind between orders and customers.

Or is this ability available now?

Thank for your time.

View Replies !   View Related
SSIS With Transaction
Hi

I new in SSIS. I tried to do the simple package with transaction support.

I do the sql taskwich "delete from TableName" and then i do a simple data flow from one table to "TableName" which i deleted from databefore.

I wanted to use transaction that in case the data flow collapse the transaction will roll back the delete task of table.



But then i exucute my package it stucks like the table is locked.

Can any one help me with this package?

Thanks.

View Replies !   View Related
SSIS Transaction
I am just started working on SSIS. I created one package and want to impliment the transactions.

The purpose of the package is to truncate the destination table and insert the records into destination table from flat file

i taken execute sql task to truncate the table and dataflow task to insert the records into destination table.

at the package level i given transaction option as required and isolation level is readcommitted.

when i run the package my execution stops at dataflow task and it is always yellow.

If i am using delete statement instead of truncate than it is working fine.

is truncate table creating any problem? I need to use truncate.

please suggest me how to impliment transaction using truncate and dataflow task.






View Replies !   View Related
SSIS DTC Transaction's HOT Questions
I am using SSIS with Transaction, and Imeta lot ofquestions asbelow:

1.IF DTC doesn't support ADO.NET Connection?

Please check the ADO.net Connection's property, it has a property named like: DTCTrancactionSupport. But it's always set to False and unable to edit.

IF the answer is NO, HOW could I bound all my Tasks into one Transaction? You know, I havesome Tasks with SQL connection, and I have also some Script Tasks, witch need the ADO.net connection.

2.IF DTC required to run in both the Destination Server and the Source Server?

I have a Dataflow Task, and take some data from a table in aSource Server to a Destination Server. The DTC doesn't run on the Source Server. I found the Dataflow Task hangs when I use the SSIS Transaction(just set the Task's transactionOpion to Required, Only one Task in the Package).

3.IF DTC doesn't support RetainSameConnection?

Many people say that. Really?

4.When a DTC transaction is running, if the table in it could not be modified by process outside of the transaction? For example,a package with a DTC transaction, and there is a SQL Task inside, when it is running, could Imodified the same table's datamanually(in the Sql Sever Management Studio)?



View Replies !   View Related
Transaction Causes Hanging Of SSIS
Hi

I set TransactionOption=Required for my SSIS package. In this package, 2 tasks are included,

one is execute sql (TransactionOption=supported)

2nd is DataTransformation Task (TransactionOption=Supported)

But it hangs on the execute sql task itself!

Didnt find any reason 4 it so far!

View Replies !   View Related
Transaction With SSIS / SQL Server / DB2
Hi,

very simple task... I want to copy some data from a DB2 table to SQL Server. So I want to delete the records in the SQL Server table and copy all data from DB2 to the SQL Server. That's easy...

The only thing I want to make sure is that in case of an error I still have a valid dataset in the table. So my idea was to use a transaction covering the delete and the data flow.

So I moved the two tasks in a container, switched transactions to "required"...

What I see is that now a distributed transaction is started for DB2 and the SQL Server. But that's not what I want (and it doesn't work also...). I don't need the transaction on the DB2 side, I just want it on the SQL Server to cover the delete and the insert.

What can I do?



Thanks,

View Replies !   View Related
SSIS Transaction ERROR
Hi all,
I'm having an issue with transactions in SSIS.


I have a Sequence Container, that contains 5 Tasks.



1) Data Flow Task using an OLEDB Connection to access an Oracle RDB database via a linked server. then insert into SQL Server 2005.

2) Execute Sql Task on Sql Server 2005 (UPDATE Statement)

3) Execute Sql Task on Sql Server 2005 (DELETE Statement)

4) Data Flow Task using an OLEDB Connection to insert data into a SQL Server 2005 DB

5) Execute Sql Task performing an UPDATE statement on an Oracle RDB database using an OLEDB Connection to to access the database via a linked server



If the Sequence container and all contained tasks are set to Transaction: Supported (No transaction will be created I believe), then the package runs successfully.



If I change the Sequence container to Transaction: Required, and leave all contained tasks as Transaction: Supported, then Task 1 fails with the following error:



[SELECT From RDB Change Table [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SSISPOC" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.



Any Ideas would be much appreciated, as I'm stumped!






View Replies !   View Related
Setting The Transaction Inside A Script Task In A Child Package
I have what I would consider an odd situation that I really need to be able to solve and can't find any information. I have a parent package that is executing a child package, now in that child package I have a script task that is updating some records among other things. I need to know how to tie the updates that are happening in that script task to the transaction in the parent package so that if anything goes wrong anywhere in the package those updates are also rolled back. Can anyone help?

Thanks,
Jason

View Replies !   View Related
Truncation Of Transaction Logs Using SSIS

HI,

What I have set out to do is to try and truncate the transaction logs on my server instances after a nightly backup. I went ahead and used the shrink database option in the maintenance plan wizard. Unfortunately I have found this will only truncate the logs for databases using the simple database recovery model, and I wish to truncate the whole lot to save space...

I went ahead and put together an SSIS package that does the following:

1. First thing I do is build a fresh copy of a database listing in a temp table using the following SQL statement

USE Master;
Go
SELECT name, DATABASEPROPERTYEX(name, 'Recovery') AS RecoveryModel, DATABASEPROPERTYEX(name, 'Status') AS DBStatus INTO TempTables.dbo.Recovery FROM sysdatabases ORDER BY name
GO

2. Next I read this dataset back into memory using a SQL statement as follows:

USE TempTables
GO
Select name from dbo.recovery Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb'

I map the results to a results set called User::TableName (which is of an object data type)

3. Next the package goes to a ForEach Loop container and I use the Foreach ADO Enumerator Enumerator to read the data I just selected into the look. I select the User::TableName variable and use the enumeration mode 'Rows in first table'. I have used a variable mapping of Variable: User::TableName with an Index 0.

4. I then use the following SQL statement (which sits in the For Each Loop) to try and alter the recovery model:

ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
GO
This is where the package falls over with the following error message

SSIS package "mnt_TransLog.dtsx" starting.
Error: 0x0 at Simple Mode: Incorrect syntax near '@TableName'.
Error: 0xC002F210 at Simple Mode, Execute SQL Task: Executing the query "ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
" failed with the following error: "'RECOVERY' is not a recognized SET option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Simple Mode
Warning: 0x80019002 at For Each Table Set to Simple: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at mnt_TransLog: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "mnt_TransLog.dtsx" finished: Failure.

I think the problem here is either I am not passing the variable across correctly (i.e. not declaring it or something..!) or I am passing the wrong type of variable across.

Once the recovery model is changed I can truncate the transaction logs successfully but I cant seem to until this is done! Any help in finding asolution to this problem or a better way of approaching this problem would be appreciated!

Thanks

Marek Kluczynski
Management Information Officer
Investors in People UK
7-10 Chandos Street
London
W1G 9DQ
Tel: 020 7467 1956



View Replies !   View Related
Roll Backing The Transaction In SSIS
Hi



I have a situation where i need to insert or update the data from a flat file to a sql server database. The flat file contains nearly one lakh records.



I am using transactions. If all the rows are inserted or updated successfully i am commiting. If there is any error i am rollbacking the transaction.



when rollbacking the transaction it is taking more than 3 to 4 hours.



Can any one suggest me how to do that in batches?



Thanks in Advance



View Replies !   View Related
SSIS Error - Transaction Context In Use By Another Session
All

I'm developing a package in SSIS reading data from two databases on a SQL 2000 server and moving the data (with some transformation) to a SQL 2005 server. I am executing the package on the 2005 box. The package must employ transactions and having configured MSDTC on both servers I still seem to get the abovementioned error when setting the TransactionOption value to required for the control.

I do the following inside a Data-Flow-Task:

Create and OLE DB Source connection that points to a connection manager configured against databaseA on the SQL 2000 server. The following SQL example applies:

Select col1, col2...coln
from databaseA.dbo.table1

Create and OLE DB Source connection that points to a connection manager configured against databaseB on the SQL 2000 server. The following SQL example applies:

Select col1, col2...coln
from databaseA.dbo.table1

Both of the OLE DB Source outputs are directed through a Union Transformation (since the structure of databaseA and databaseB are identical - only the data in each database is different).

The output of the union is directed to a Multicast Transformation. The multicast directs it's output to two seperate OLE DB destinations.

Destination1 - SQL Server 2000 table is populated with multicast output stream 1.
Destination2 - SQL Server 2005 table is populated with multicast output stream 2.

Both of the destinations above have their own connection managers.

When I execute the data-flow-task I get a clutch of errors as follows. If I simply set the TransactionOption to 'NotSupported' or 'Supported' instead of 'Required', this task executes successfully. Also, if I split the task into two seperate data-flow tasks (ie: select from databaseA and write Destination1 in one data flow and select from databaseB and write to Destination2 in another data flow - the package will succeed).

I have done many searches on MSDN/Technet/Google etc. to find a solution here. The bottom line is, I need to employ a transaction in this package and haven't been ab;e to get it working despite haviung broken the package down to bare bones and creating a dummy environment

Error: 0xC0202009 at Insert Unprocessed Keys From DatabaseA & DatabaseB, Write Keys - SQL_DEST_2000 [104]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Transaction context in use by another session.".
Error: 0xC0202009 at Insert Unprocessed Keys From DatabaseA & DatabaseB, Write Keys - SQL_DEST_2000 [104]: An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E37 Description: "Transaction context in use by another session.".
Error: 0xC004701A at Insert Unprocessed Keys From DatabaseA & DatabaseB, DTS.Pipeline: component "Write Keys - SOURCE" (104) failed the pre-execute phase and returned error code 0xC0202040.

Thanks in advance

David

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved