Task:
You are working an ETL developer in an organization; you are
responsible for creating different SSIS Packages for data loads and keep them
running smooth in production. In case of failure, figure out the issue and work
the team to solve the problem.
So how it starts?
All Lazy:
What do I mean by that?
I have worked in
organizations where people develop SSIS Packages, Deploy SSIS Packages and then
forget about them. Nobody noticed that the Job is failing every day till
business users stop by to tell you that they are not finding updated data.
Smart DBA:
If your organization has the team of DBAs or a DBA who
really take care of things seriously. When the job will be created for your
SSIS Package then he will add notification to it on failure. So if your package failed at night or day,
DBA will be the person who will notify you that the job has failed with some basis error information that he received or got from SQL Server Agent Job History.
Smart Me!
As ETL developer if I am smart and follow best practices, when
I deploy my SSIS Package, I will add following items to it
- · On Failure Email Notification
- · Logging for detail error information
If I have done that then if any of SSIS Package fails, I
will get the email. I can add the team members in email so if I am not around then other developers do get the email and start looking into the problem.
From Where to Get Error Information?
Here are the different ways to get error information once
your SSIS Package failed.
- If you have not set up error email notification in your SSIS Package, Then DBA will be sending you the initial information from Job History (SQL Server Agent Job). Your company might be using different scheduler than SQL Server Agent. Most of the scheduler those I have worked with do provide Job History and Error Message Information.
- If you have set up the Error Email notification from SSIS Package itself, you will get the email from your SSIS Package with error information. Here is the link how to set up that.
- If we want to further look into error details, we need to check the logging file or log records in table. SSIS Provide different type of logging such as text file, SQL server table, xml etc. Here is the link how to set up Logging in SSIS Package. You should have access to see the information. If your SSIS Package is logging execution information to file system, then you need read permission to the folder where the log files are created. If you are logging information in SQL Server Table then you need read permission on table. When you deploy your Package to Production, Make sure you get the permissions so when your SSIS Package fails, you can go to those locations and look into log files or table to find out error.
What’s next?
Once you have all the error information
then you take steps accordingly
Let’s consider few of common issues
1-Connection Failed:
If package was not able to make connection
to Database or file due to network issue, you can ask the DBA to rerun the SSIS
Package after making sure it is saved to rerun.
2- File is being used by Another Process:
If your SSIS Package is reading some Excel
file to load data and that file is opened by another user, you can get this
type of error. In those scenarios, you have to find out who has opened the file
and then ask them to close the file or make a copy and leave the original for
SSIS Package to use.
3-Row yielded no match during lookup:
Your incoming data could not find the
matching record from lookup and you have configured the lookup to fail in case data
does not match. In this case you can make sure the lookup table (reference
Table) has the required data for match.
These were few of the examples just to
start debugging. There could be problems in which you have to take your SSIS
Package from Production and run with same data in UAT/QA to further debug
issue. You can use Break Points, Data Viewer etc. to further look into the
issue by debugging your SSIS Package in BIDS or SSDT.
To summarize this topic, I will suggest my
fellow ETL developers to implement Error Notification in SSIS Package and ask
the DBA to add notification to Job on failure. Always have Logging setup for
your SSIS Package. If you have above in your SSIS Package, Finding Error will
be much easier and less time consuming.