SQL Server BI step in SSIS 7 (end)

SQL Server BI step with step SSIS 7 (end)-transaction, error output, event handling, logging

As with other programs, SSIS packages need to be robust and stable to run, and such programs are reliable and scalable. SSIS provides support for the following:

1. Transaction: A package can be set to one or more transactions, or even two packages can be set to a transaction. To ensure data consistency, you can also DTC transactions or SQL Server engine-level transactions.

2. Checkpoint: The operation of a task when a package error is logged, so that when the package starts again, it is directly executed directly from the task where the error occurred.

3. Error output: Even the perfect program will have errors, especially for the task in the data flow, and may be due to the format, type and other problems, resulting in this line of error. SSIS allows error-output processing for each row, and you can fix or record it, and of course you can ignore it.

4. Precedence constraint: In the control flow, you can use not only the success of a task, failure or execution as a condition to perform the following tasks, or even use an expression as a condition.

5. Event handling: In SSIS packages, event handling is tied to control flow and data flow. Here, you can handle different events for packages, tasks, or containers, and events can also be used to set breakpoints and control logging. We can use the OnError event to catch the error.

6. Logging: You can log the specified event information at run time, either in local text or in an XML file, or in a database, or in a Windows Eventlog,profiler file, even if you can extend your own logging.

Demand:

In order to integrate with other systems, the AdventureWorks system's product price is updated in real time, it needs to download a compressed file from a specified URL, and after decompression, analyze and import the data file. Because the downloaded file is a non-program maintenance of the file, due to human factors, the data format there may be errors, but the request to ignore such data. In addition, if processing fails, the retention history file is required. If an error occurs, you need to send an error email. You also need to keep the program log to find the cause of the error.

Realize:

1. Under your D disk, create a new Productprice folder to store the compressed and data files, and create a new bak directory below to save the files.

2. Create an SSIS package with the following new variables:

change name	type	value	description
filepath	d:\productprice\< /td>	data root
datapath	string	d:\productprice\productlist.txt	after decompression data file
string	@[user::filepath] + "bak\\" + (DT_WSTR, 4) year (GETDATE ()) + "-" + (DT_WSTR, 2) MONTH (GETDATE ()) + "-" + (DT _WSTR, 2) Day (GETDATE ()) + ". zip"	is primarily used to store compressed data files that were not executed successfully, with the name of the current date ( The current date is evaluated by an expression such as: D:\ProductPrice\bak\2009-9-20.zip
winrar	c:\program Files\Win Rar\winrar.exe	zippath	d:\productprice\ Price.zip	errocount	0	number of rows in data file parsing error