SSIS – Using Breakpoints

Using Breakpoints

Much like running projects in other languages through Visual Studio, you can also stop SSIS packages mid-execution when specific events occur in the Control Flow.  This ability allows you to stop the execution of a package to investigate and troubleshoot the state of the SSIS package.

There are ten events in the Control Flow (definitions from Books Online):

• OnPreExecute: Called when a task is about to execute. This event is raised by a task or a container immediately before it runs.
• OnPostExecute: Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
• OnError: Called by a task or container when an error occurs.
• OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
• OnInformation: Called when the task is required to provide information.
• OnTaskFailed: Called by the task host when it fails.
• OnProgress: Called to update progress about task execution.
• OnQueryCancel: Called at any time in task processing when you can cancel execution.
• OnVariableValueChanged: Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
• OnCustomEvent: Called by tasks to raise custom task-defined events.

I’ve mentioned this twice now, and thought it should be called out yet again.  Breakpoints function only at the Control Flow.  If you need to break the action in a Data Flow, this isn’t the solution for that.  This is called foreshadowing.

Breakpoint Demonstration

To implement a breakpoint in the Control Flow, start by right-clicking on a task in the Control Flow.  In the menu, the option to Edit Breakpoints will be about half way down this list.

Select this menu item and you can choose between any of the breakpoints described above.  For this example, we’ll select the OnPostExecute breakpoint.  As mentioned above, this event will fire after the task’s execution has completed.

Let’s run the package now to see what happens.  First you’ll notice that the the task has a red rot added to it, this indicates that there is a breakpoint on the task.  Next when the breakpoint is reached, a little yellow arrow is added to the breakpoint icon.

Once a breakpoint has been used there are a number of things that can be used to get some information on the breakpoint.  To start with there is the Call Stack window.  This window provides information on where the breakpoint is located.

Next there is the Breakpoints window.  Guess what it has?  Yes, all of the breakpoints in your project will be listed here.  There are couple points of interest and points of usefulness with this window.

First, the area in blue is all of the breakpoints in your package.  From this point you can enable and disable them.  A very useful feature in case you have many breakpoints in your package.

Second, the area in red is a control panel for your breakpoints.  There are options to delete single breakpoints, delete all breakpoints in a project, disable all breakpoints in the project, and change the columns returned.  There is additional functionality that can be used as well in advanced scenarios.

There is also an Output window that displays messaged on the progress of the package and breakpoint.  This information will help determine what has been run in the package when the breaks occur.

Finally, the most important piece that breakpoints bring to you is the Locals window.  This window will contain information on the current status of the package execution and the amount of time in which the package has been executing.

Most importantly, it contains all of the package variables.  Often when I need to troubleshoot some odd package behavior it is related to variables not being populated as I thought they should.  This is the golden ticket to finding out what the package is doing during execution.

Breakpoint Wrap-Up

Breakpoints are an essential tool in troubleshooting SSIS packages.  Take time to understand how they work and practice using them a few times so that when the time comes you are ready to pull them out.  They’ve gotten me through a few package executions and will probably save the day for you some time in the future.

This post scratches the surface on using breakpoints.  There is a lot more that can be covered and other scenarios for their use.  But those will have to wait for another day.  Maybe one of the other 29 more posts in this series.

REFERENCES

Breakpoints in SSIS

Breakpoints in SQL Server 2005 Integration Services SSIS

Thank You

SSIS - Raw File Format

Raw File Format

Starting with the definition, the raw file format is the data format that is native to SSIS.  When data is being consumed by the data flows of an SSIS package it has a format.  This format is the same that appears in raw files.

Since these are the same file format, SSIS doesn’t have to parse or translate the data between the data streams or the raw file format.  This results in the raw file format being faster than most other data source and destination formats

The raw file format can be incredibly useful.  It can be used for either a data source or a data destination.  Once you’ve gotten your hands wet with them, you’ll find a great many uses.  Some of which may solve issues that you’ve run into with previous development projects.

For instance if you want to build an SSIS package that processes data in multiple steps, temporary versions of the data can be stored in raw files and passed between data flows.  This will allow packages to be built such that they can be restarted at different points in the data processing.

Similar to the previous example, raw files can be used to exchange data between SSIS packages.  You can develop one package to convert a clients file to a standardized structure in the raw file format.  And then use a couple other packages to read that format in the next packages that process the data in a standard format.  All of this can be done without hitting the database.

Another thing you can do is export data from a database to a raw file to use a reference file for multiple SSIS packages.  If you need to cycle through a group of IDs or states multiple time, store the data in a raw file and use it as a data cache.  Sure you can use a Lookup Cache instead – but that doesn’t fit all scenarios and not everyone is using SQL Server 2008.

Let’s move onto a couple demonstrations

Raw File Format Destination

First off, let’s build an SSIS package that uses a Raw File as destination.  We’ll fast forward through adding the Data Flow and the OLE DB Source.  The query for the source doesn’t matter for this demonstration so we’ll skip over that too.

Let’s begin.  Start by dragging a Raw File Destination onto the Data Flow and connecting the OLE DB Source to it.  The package should look like this:

From here, open the Raw File Destination so that we can configure it.  The first thing you will need to configure is the location for the raw file.  This can either be a static value or tied to a variable.  In most cases I use variables to improve configurability between environments

Next you’ll configure how you want the Raw File Destination to behave when it writes.  The write option selection is important since it will have significant impact on how the destination is populated

There are a few options for this, they are (definitions from Books Online):

• Append: Appends data to an existing file. The metadata of the appended data must match the file format.
• Create always: Always creates a new file.
• Create once: Creates a new file. If the file exists, the component fails.
• Truncate and append: Truncates an existing file and then writes the data to the file. The metadata of the appended data must match the file format.

Missing from this list is a Create First Time or Create Metadata option.  As you begin to use the file format more, you’ll realize that this is an important option to have.  Please, help out by voting for a metadata option for raw files.

At this point, you can move to the next screen and select the columns you need.  There is a select all checkbox for SQL Server 2008 and forward, which is quite handy.  For SQL Server 2005, you need to select all of the columns individually.

One extremely nice thing about the Raw File Destination is that the order of the columns in it don’t matter.  Whether you add a column now or in six months it’s position in the raw data file doesn’t matter.  Or if you forget to add a column that data source expects and it is setup for the source as the first column.

Execute the package and you will have a Raw File created.

Raw File Format Source

Now that we have a file to work with, let’s create a quick package that can import some data from a raw file.  As before, we’ll skip going through adding the Data Flow and then Raw File Source and Row Sampling task.  The Row Sampling task is just there to provide a place to add a data viewer if you desire to see the data that is returned.

Configuration for the the Raw File Source is similar to the Raw File Destination.  Open up the data source and first you determine the location for the raw file.  Same options as above with either be a static value or tied to a variable.

Next you’ll select the columns that you want returned.  They can be selected through the check boxes or by typing their names in the grid below.  The column names can also be changed at this point in the Output Column column.

At this point the Raw File Data Source is configured.  It knows the underlying data so there isn’t any need to call up and discuss this with the person that created the raw file, if it isn’t you.  The steps are simple and not likely to get you bogged down in details – since there really aren’t any.

Raw File Wrap-Up

Now that we’ve got through this far, I hope that you see some of the value is using the raw files.  I’ve got a couple upcoming posts in this series that will go deeper into their actual use, but for now this should get you started.

One bit of advice that I’ll leave you with.  After the data types for you data source or data destination are set it is wise to change the ValidateExternalMetadata property to False.  This prevents some pesky data type and file availability issues that can come up during validation and helps prevent accidentally changing data types.

References Download Package

Thank You

Tips To Get A Salary Increment

Asking for a pay hike can be a tricky affair. Even after a lot of discussions with your boss, you might end up having nothing more than mere assurances.

Your appraisal depends on a lot of factors, some of which you have no control over. A recession hit market, company's financial loss, etc are examples of some such reasons.

However, there still a whole lot of things that are indeed in your control and you can try to maneuver these factors to get the kind of hike that you are looking at. Here's what you can do:

List down your responsibilities:

Make a bulleted list of all the responsibilities handled by you in the last one year. Do not miss out on any task. Then try explaining the problems faced by you while performing them and the way adopted to overcome the difficulties.

Self initiated efforts are always acknowledged with respect. Therefore make sure you dedicate enough time for the list.

Make a note of the extra work:

It is important to have undertaken additional responsibilities in your work-life. This not only shows your dedication towards work but also the capability to accommodate more work within the allotted time. Your willingness and confidence will help your boss to build trust in you.
Speak about the added skills: 
Sometimes you need to learn certain skills do be able to perform your current job better. Point out any skill-building activities you've done or professional training courses you've taken. Any knowledge transfer or training to other team members should also be spoken about during the performance review meetings.

Prepare yourself:

Collect your thoughts and prepare yourself for the meeting. Get your points ready and keep all inferences to work at the back of your mind. Remember to use positive terms and sound confident in the meeting. Present your case clearly and logically.

Talk to the decision-maker, not HR, if you can: 

Often people throw themselves into negotiations with people who don't have any power over pay. It's a waste of time. Many times the company appoints a person from the HR team to have pay discussions, who might then pass pay rise requests up the line, but often key messages are lost or distorted. So try and find out who are the key decision makers and also look at ways that you can speak with them.

Get the data:

It is important to know what others in similar positions are being paid by competitors and the employer in question via salary surveys from recruiters or word of mouth.
This will give you an idea how less or more you are getting in terms of market competition.

Pick The Right Time:

People pick the right time and a quiet, private place for having a salary discussion and book a meeting rather than casually stopping by to ensure they get the decision-maker's attention.
You don't want to be going in when the person you are dealing with has had bad news or there's a crisis in the organization. The right time will also depend on the work cycle in the organization.

Don't just accept the first offer:

Whether you should take a first offer from an employer depends on what was offered and your circumstances.

Read the person's mine you are dealing with for salary negotiations and be open to accepting a good salary offer, particularly in the current economic climate.

But in senior executive positions, sales roles, and other jobs where people are expected to be good at bargaining, taking a first offer can be a big mistake, as employers tend to anticipate bargaining in such roles and build this into their first offer.


Be ready. Don't wait for someone to recognize your achievements. Step forward and request for your promotion.

Thank You

What are the types of triggers?

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and

ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

:- Lalit Bhutka

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE: It is responssible to declare a variable as tables structure type where as

%TYPE: it is responssible to declare a variable as tsbles column type.

:- Lalit Bhutka

Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

Linked Server Transaction error Msg 7395, Level 16, State 2, Line 273

If you are using nested transaction in your stored procedure you will get the same error. Solution to this error is to SET XACT_ABORT  TO ON;  in your stored procedure.

:- Lalit Bhutka