Using Transaction in SSIS
Categories: Database; Tagged with: SSIS • Transaction; @ October 30th, 2012 18:07Transaction is a build-in feature in SSIS, all containers(i.e, package, sequence container), and components support transaction. Support Level: NotSupported, Supported, and Required.
-
Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
-
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
-
NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
— http://msdn.microsoft.com/en-us/library/ms137690.aspx
For example:
Here is a sequence container, 3 steps: ‘Excute SQL Task’ to clear destination data, ‘Data Flow Task’ load data from source and insert data into destination, ‘Script Task’ just a script task which will fail always.
Without transaction:
config the sequence container property: TransactionOption = NotSupported, all components TransactionOption keep default(Supported), then run the package:
the first two component run successfully, the destination table has been cleared and the new data inserted, even through the last task failed.
Using transaction
Change ‘Sequence Container’ TransactionOption=Required, then run the package.
The last one failed, because they are in one transaction, so the destination will keep no change.
SSIS 使用EventHandler处理错误 Error Handling using Event Handler
Categories: Database; Tagged with: ETL • SSIS; @ October 29th, 2012 12:02 需求：
某组件/package发生错误时进行发送邮件/通知等响应操作
解决：
1. 配置组件/package： 必要时配置FailPackageOnFailure = true/ FailParemtnOnFailure=true；
2. 创建Handler：切换到EventHandlers 页面， 选择Handler的执行范围，拖拉控件进行设计。
SSIS Difference between OLE DB Source & DataReader Source, OLE DB Destination & SQL Server Destination
Categories: Database; Tagged with: Database • ETL • SSIS; @ October 29th, 2012 11:41OLE DB Source and DataReader Source (formerly known as ADO.NET source)
The OLE DB source and ADO .NET source are so similar in fact, it is forgivable to ask why they are even separate components. The simple(ish) reason for this is that the two components talk to their underlying data sources in very different ways – the OLE DB source will talk directly to relational OLE DB compliant sources, but the ADO .NET source goes through a layer of abstraction so it can talk to the source through a .NET provider. If all that sounds like gibberish, don’t worry. You only need to consider using the ADO .NET source in two cases. Firstly if no OLE DB provider is available – e.g. if you need to talk to a SAP BI instance. Secondly if you need to access the source through a Script Task.
from:http://www.bimonkey.com/2009/05/the-ado-net-source-and-sql-in-the-script-task/
And I think the difference between destinations should same as source.
OLE DB Destination v.s. SQL Server Destination
The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination. __http://msdn.microsoft.com/en-us/library/ms141095.aspx
SQL Server Destination
The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.
Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.
OLE DB Destination
The OLE DB destination supports all of the bulk load options for SQL Server. However, to support ordered bulk load, some additional configuration is required. For more information, see “Sorted Input Data”. To use the bulk API, you have to configure this destination for “fast load”.
The OLE DB destinationcan use both TCP/IP and named pipes connections to SQL Server. This means that the OLE DB destination, unlike the SQL Server destination, can be run on a computer other than the bulk load target. Because Integration Services packages that use the OLE DB destination do not need to run on the SQL Server computer itself, you can scale out the ETL flow with workhorse servers.
— http://msdn.microsoft.com/en-us/library/dd425070(SQL.100).aspx
Using for loop in SSIS
Categories: Database; Tagged with: ETL • SSIS; @ October 25th, 2012 18:07
Like for loop in any language, the for loop component in SSIS has three for loop parameters:
InitExpression, EvalExpression, AssiognExpress.
Step1: create a new variable: Counter, int;
Step2: Drug and drop a for loop component, set the properties:
Step3: Drug a ‘Script’ component into the for loop, codes: we’ll add 1 every time.
Dts.Log("Counter: " + Dts.Variables("User::Counter").Value.ToString, 0, emptyBytes)
Dts.Variables("User::Counter").Value = CInt(Dts.Variables("User::Counter").Value.ToString) + 1
run, the package, check the log:
User:ScriptTaskLogEntry,…,25/10/2012 6:02:51 PM,25/10/2012 6:02:51 PM,0,0×00,Counter: 0
User:ScriptTaskLogEntry,…,25/10/2012 6:02:52 PM,25/10/2012 6:02:52 PM,0,0×00,Counter: 2
User:ScriptTaskLogEntry,…,25/10/2012 6:02:52 PM,25/10/2012 6:02:52 PM,0,0×00,Counter: 4
Ref: http://www.julian-kuiters.id.au/article.php/ssis-how-to-configure-for-loop-control
SSIS: Logging message in Script task
Categories: Database; Tagged with: Database • SQL Server • SSIS; @ October 24th, 2012 15:24The Script task can use the Log method of the Dts object to log user-defined data. If logging is enabled, and the ScriptTaskLogEntry event is selected for logging on the Details tab of the Configure SSIS Logs dialog box, a single call to the Log method stores the event information in all the log providers configured for the task.
Pre-condition:
Loging is enabled and ScriptTaskLogEntry is selected:
Loging code:
Dim emptyBytes(0) As Byte
Dts.Log("TotalRecords: " + Dts.Variables("User::NoForCheck").Value.ToString, 0, emptyBytes)
'Error:
Dts.Events.FireError(0, "", "Can not find the source file!", "", 0)
Dts.TaskResult = Dts.Results.Failure
Return
Log:
User:ScriptTaskLogEntry,,,24/10/2012 3:16:22 PM,24/10/2012 3:16:22 PM,0,0×00,TotalRecords: 27
API:
Logging in the Script Task
http://msdn.microsoft.com/en-us/library/ms136131.aspx#Y272