November 7, 2006 at 12:22 pm
0) We are migrating data from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
1) We have an data flow task SSIS package.
2) We managed to send a mail with various variables & their values in subject or body of mail.
3) How do we send mail with details of Error/Warning using OnError/OnWarning events?
4) Can we have bad tables i.e where records which failed to migrate get inserted into. If yes, what are the steps to implement this.
5) Can we have a mix of in-built & custom logging. Is logging provider necessary to do logging. What logging provider to use?
6) Where are the errors that come during build, deploy, install and execute stored? Is there a log file(s) & what it their location?
7) Please post links if that is better.
November 8, 2006 at 2:47 pm
Hi Koneru,
"3) How do we send mail with details of Error/Warning using OnError/OnWarning events?
6) Where are the errors that come during build, deploy, install and execute stored? Is there a log file(s) & what it their location?"
Since these 2 questions are related, I grouped them together.
Every package gives you the ability to produce a log file for it. That log file tracks and stores the results for a wide variety of events including OnError, OnPostExecute, OnProgress, etc.
In addition, you can determine the way that each event is monitored by selecting different types of logging providers. For ex., you have the option of choosing between SQL Log Provider for Text or SQL Log Provider for SQL Profiler, etc., and these can also determine the type of information stored by the log file.
In order to enable a pkg to produce and/or update a log file, open your pkg in BIDS, go to the Control Flow window, and right click anywhere where there is open space.
Select Logging from the Short-cut menu, and then configure your log as you wish. Remember to specify a file name and location by clicking in the Configuration box.
If you need more information about the Logging feature, just read up on it in SSIS help.
When you run your pkg, it will update the log file that you specified in the Logging Configuration.
You can use the Send Mail Task in SSIS to attach that file to an Email that is produced once an event occurs in your pkg (i.e., on pkg completion run Send Mail Task).
"4) Can we have bad tables i.e where records which failed to migrate get inserted into. If yes, what are the steps to implement this.
5) Can we have a mix of in-built & custom logging. Is logging provider necessary to do logging. What logging provider to use?"
I think that these two topics are potentially related. Basically, you can design whatever auditing and logging tables that you want, and then use SSIS's error handling features to populate them. Please note that this is entirely different than creating a log file which is what a logging provider is used for.
The data flow components in SSIS are a perfect place to start exploring SSIS's error handling features.
For ex., if you use the Flat File Source Data Source component, you can double-click on it to open up it's editing window. In that window, you'll see Error Output as one of the options in the left pane. That Error Output window permits you to set error handling at the column level. You can choose to fail the component, ignore the failure, or redirect the failed row.
Once you configure the error handling for the desired rows, you can go back to the Data Flow window, select a data destination source, connect the Flat File Source component to it using the Error Flow component. Map the columns produced by the Flat File Source component to your destination table.
Now when an error occurs it can be stored in your error log table. In addition, if you select the redirect option in the components Error Output property, then it will send the data from the row that failed to that destination table.
From what I remember, SSIS did a good job of introducing the basics of its error handling features in the Design a Package tutorial.
I hope this gives you some ideas about what you can do in SSIS with logging and error tracking/handling.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply