SQL Server Bi step by step SSIS 7 (end)-transaction, error output, event processing, logging

like other Programs , SSIS packages also require robust and stable operation to ensure reliability and scalability. SSIS provides the following support:
1. Transaction: You can set one or more transactions for a package, or even one transaction for two packages. To ensure data consistency, you can also perform DTC transactions or SQL Server engine-level transactions.
2. checkpoint: used to record the running status of the task when a package error occurs, so that when the package starts again, it is directly executed from the task where an error occurs.
3. error output: even if the program is perfect, errors may occur, especially for tasks in the data stream, and errors may occur due to format, type, and other issues. SSIS Allows error output processing for such lines. You can fix or record the errors. You can also ignore the errors.
4. priority constraint: In the control flow, you can not only use the success, failure, or execution end of a task as a condition to execute the following task, but also use an expression as a condition.
5. Event Processing: In the SSIS package, event processing is parallel to control flow and data flow. Here, you can process different events of a package, task, or container. The event can also be used to set breakpoints and control logging. We can use the onerror event to capture errors.
6. log Record: you can record the specified event information at runtime. It can be stored in local text or XML files, or saved to the database, or in Windows Eventlog or profiler files, you can even extend your logging.

Requirements:
To integrate with other systems, the product price of the adventureworks system is updated in real time. You need to download a compressed file from a specified URL, decompress the file, and analyze and import the data file. Because the downloaded file is a non-Program-maintained file, due to human factors, the data format in the file may be incorrect, but such data must be ignored. In addition, if the processing fails, you need to keep the historical files. If an error occurs, you need to send an error email. At the same time, you need to keep the program log to find out the cause of the error.

Implementation:
1. Create a New productprice folder under your d drive to store compression and data files, and create a Bak directory to store backup files.
2. Create an SSIS package and create the following variables:

Change name	Type	Value	Description
Filepath	String	D: \ productprice \	Data root directory
Datapath	String	D: \ productprice \ productlist.txt	Decompress the data file
Backpath	String	@ [User: filepath] + "Bak \" + (dt_wstr, 4) year (getdate () + "-" + (dt_wstr, 2) month (getdate () + "-" + (dt_wstr, 2) Day (getdate () + ". zip"	Set evaluateasexpression to true. It is mainly used to store compressed data files that fail to be executed. Its name is the current date (the current date is calculated by the expression) For example: D: \ productprice \ Bak \ 2009-9-20.zip
WinRAR	String	C: \ Program Files \ WinRAR \ winrar.exe	WinRAR installation directory
Zippath	String	D: \ productprice \ price.zip	Zip compressed file download path
Errocount	Int32	0	Number of lines in data file parsing error

3. Create an ole db connection to connect to our adventureworks database.
4. Create an HTTP connection, and enter the location of the remote compressed file in the URL (of course, variables can also be used here ).

 

 

Of course, creden or certificates may be used here.
5. Add a script task in the control flow to complete the file download and set the read variable User: zippath. Edit script:

Public voidMain ()
 

 
{
 

 
// Todo: add your code here
 

 
ObjectNativeojbect = DTS. Connections [0]. acquireconnection (Null);
 

HttpclientconnectionConnection =NewHttpclientconnection(Nativeojbect );
 

StringFilename = DTS. Variables ["Zippath"]. Value. tostring ();
 

 
Connection. downloadfile (filename,True);
 

 
DTS. taskresult = (Int)Scriptresults. Success;
 

 
}
 

run the package and you will find that the rarfile can be downloaded to a local folder.
6. to decompress the package, make sure that the WinRAR application has been installed locally. Add a process execution task, set the property windowstyle to hidden, set the expression, and set the properties as follows:

attribute	Expression	description
executable	@ [user: WinRAR]	WinRAR path of the executable program
workingdirectory	@ [user: filepath]	execution directory
arguments	"E" + @ [user: zippath] + "*. txt-y"	In the winrarcommand line, extract all the. txt files in the @ zippathfile, and y indicates that if the file exists, directly overwrite the file

In the execution package, two files, one zipzip file and one productlist.txt file, are generated under the productpricedirectory.
7. Create a new file connection pointing to the D: \ productprice \ productlist.txt file, select Unicode, and set the column. However, our file path is a variable and cannot be written here. Therefore, in the attribute, set its expressions and set the connectionstring value to @ [user: datapath].
8. Add a Data Flow task. In the data flow task, first add a flat file source to connect to the file connection we just created. There are only two columns in the file, one is the product number and the other is the product price. Both are of the dt_wstr type.

Prdocutnumber listprice
AR-5381 22.0
BA-8327
Be-2349 12
Be-2349
Be-2908 122.2
To add a data conversion task, we convert the product price to the numeric type:

When a program package is executed and an error occurs, it is converted into a data conversion task. For an empty string, it cannot be converted into a number. What should I do? ClickConfiguration Error outputHere, we can specify a row-level error and select the row with the error:
1. component failure: the entire task fails to be executed.
2. Ignore failed: Ignore this row error. For this task, the value of pricelist in the ignored row is null.
3. Redefine the row: Failed data. You can redefine the output.
Here, we choose to redefine the line:

Similarly, we can also handle the same error output for the above file connection to prevent data errors during file reading.

9. Add an ole db command to update the data. Sqlcommand:

UpdateProduction.ProductSetListprice=?WhereProductnumber=?
 

In the column ing column, map two parameters. Pay attention to the order before and after.
10. Add a row count task. Note that the red errors are dragged under the data conversion task and output to the row count, so that the row data with errors will flow into this task. Set the variable of the row counting task to @ errorcount. Run the package again. The error is gone, and it is found that the correct direction of the data conversion is divided into three rows, and the error direction of the two rows.

 

 

11 . Add file processing. Add three file systems to execute to the control flow, analyze and delete compressed files, back up compressed files, and delete data files. Note that all variables are used (For details, refer to the download source file ). we want to execute backup compression files when the data stream fails to be executed. here we need to set priority constraints . The default value is green when execution is completed. Right-click the connection and select failed.
whether the execution is successful or failed, we want to delete the data file at the top of the button, the two File System Tasks point to the data file deletion task at the same time. However, we need to set the tasks between them to be executed after one execution is completed. As shown above, right-click and select Edit to go to the priority constraint editor. We choose logic or, one of the two tasks is successfully executed:
12. let's take a look at the effect of the Priority constraint set above. Modify the data conversion tasks in the data stream to component errors and reset the rows:

13As the number of products increases, the size of the compressed package to be downloaded is too large. When the execution fails, we hope to run the failed task directly without having to download and decompress it again.Set the checkpoings and check the checkpoint to complete this function.. First, set the three attributes of the package: savecheckpoings to true, checkpoingusage to ifexists, and checkpointfilename to set a TXT file. Note that if we want a task to be able to set checkpointsFailpackageonfailureThe property must be true. We set the data flow for analyzing the data update priceFailpackageonfailureProperty is true ,(This is actually in conflict with the above priority constraints, because now, even if the task fails, it will directly cause the entire package to fail, the following Backup and deletion tasks will not be executed. Here we just want to demonstrate different settings respectively ).Once again, we set the data in the data stream to component failure to cause the entire data stream to fail. Run the package and check that there is a lot of data in the checkpointfilename you just selected. When you run the package, the two tasks of downloading the product price compressed file and decompression file are not running, but start directly from the price of the analysis data update:

In practice, we can use checkpoint settings. When re-running the package, we do not have to re-run the task without errors, and it consumes a lot of resources and time.

14. what if an error occurs in file processing in the data stream at the price of data update analysis? Will our price update not be submitted yet? We changed the path for deleting the compressed file to user: filepath to cause an error. After comparison, we found that even if the task of deleting the compressed file has encountered an error, the above analysis data update price task also submitted changes, and the product price has been updated successfully. In fact, this is not what we want to see. We want the files under productprice to be consistent with the update processing of our database. When the following file processing errors occur, the above database updates are also not submitted. This requires a transaction. You need to set the transaction attribute of the package and the task-Level Attribute -- transactionoption to describe its value.
support if a transaction already exists in the parent object, add it.
not supported does not join a transaction even if one transaction exists.
the required transaction is required. If yes, add it to the existing transaction. If not, start a transaction.
we can see that our current task and package's transactionoption value are support, but the transaction is not started. The packet attribute is changed to required, and an error occurs again. This setting is not allowed. The prompt is:

the current package settings are not supported. Change the savecheckpoints attribute or transactionoption attribute.
In fact, checkpoings and transaction conflict with each other. The container of a transaction is the smallest unit that can be re-started and cut-in. The whole transaction is not executed, or both are executed, which meets the characteristics of the transaction. To reduce conflicts between the two, SSIS does not save its checkpoint information when a container (a package is also a container) is not in a task. In addition, checkpoints encounters the same problem in the foreach container because it cannot save the information inside the container. (Here is a detailed description of the http://technet.microsoft.com/en-us/library/ms140226.aspx)
similarly, we just to demonstrate the effect, first set savecheckpoints to false, set transactionoption to true. run the package again and compare the data at the front-end. When the compression file fails to be deleted, the price update task is immediately displayed, and the price is not updated. At this time, the distributed transaction has been started for the package.
15 . Is it true that the entire package will not go wrong? Of course not. If remote access is unavailable or the connection times out, if the local disk space is insufficient, or if an error occurs when updating the database, the entire package may be faulty again. We hope that we can actively notify us of such errors. And tell us the specific error information. Switch to the event handler. You can select two drop-down boxes to run files and event handlers. Here, we only respond to the onerror of the package and click Create below. In event processing, we can use the same task types as the control flow.
we use the same method as the previous one to send emails. We only need one script task to send emails using scripts. For details, refer to: success.

StringBody ="Package failed error :"+ DTS. Variables ["Errordescription"]. Value. tostring ();
 

16.Finally, let's set the log record. Click the SSIS menu above and select log record. Here you can also select a container and the log type to switch to the details, we can select the event to be recorded:


Detailed error information is found in the dig command. We can also extend the provider type here to make it possible to send emails to achieve the same effect as event processing, and it is more universal, so we will not go further here.

 

End:
SSIS is used to complete a practical function to introduce the transactions, checkpoints, event processing, error output, priority constraints, log records, and other aspects of the SSIS package, as well as the SSIS expressions, execute Process tasks and download remote files. SSIS is really powerful, just a few timesArticleIt is difficult to cover all its aspects. Although the end is in a hurry, we are glad that this series of SSIS has ended, I hope these simple examples will be helpful to my friends who are getting started with SSIs, and I hope we can share their strength. SQL Server Bi step by step SSRs --- Reporting Service 2008 will be started next time.

Download:
Http://files.cnblogs.com/lonely7345/DownloadRssNews.rar

Author: lone knight (like a year of water)
Source: http://lonely7345.cnblogs.com/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.