SSIS Error: Violation of primary key constraint when no duplicate exists 0x80040E2F

Scenario:

  1. I have a source table with dbo.Codes with primary key combination(CodeID, CodeName).
  2. In order to create my destination table, I have used the SQL Server–>Database–>Tasks–>Generate Scripts, option.
  3. Then, in the SSIS package, I simply used the OLE DB Source and OLE DB Destination element. It was returning error: “Violation of PRIMARY KEY constraint ‘pkCodes’. Cannot insert duplicate key in object ‘dbo.Codes’. The duplicate key value is (106, da7A).”.

What I have tried to solve:

  1. I have tried to use the sql command as source: Select CodeID, CodeName from dbo.Codes GROUP BY CodeID, CodeName. It was still returning error. I got confused at this point.
  2. After searching online, I found a tips to add a SSIS Toolbox–>Common–>Sort element between OLE DB Source and OLE DB Destination. I checked the option “Remove rows with duplicate sort values” in the Sort element. I was still getting error.
  3. Then, I had enabled the data viewer between OLE DB Source and Sort element. I could see there are two rows: 106, da7a and 106, da7A in the source.

What is the real problem?

My source table c0lumn CodeName is case sensitive but my CodeName column in the destination table is not case sensitive. This has occured because, sql server generate script option–>Script Collation is set to false by default.

Solution:

I recreated my destination table with Script Collation option: True, which made my destination column case sensitive and it has solved my problem.

 

Advertisements

SSIS – reading blank rows from Excel 2007 which are actually deleted

Folks,

We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem –

1. Go to the Ribbon, and select the Home tab.

2. In the Editing section, click the small arrow next to Find & Select .

3. Select Go To Special .

4. Select the Blanks radio button and click OK.

5. All blank columns/rows will be highlighted.

6. Delete

However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any possible workarounds using which this can be resolved in an automated manner – like a third party component maybe. I do understand the following –

1.     Excel 2007 is not really the best file format for loading data – a tab or comma-delimited file works much better. However, that is not an option here since the users are not comfortable and this decision has been already taken after many rounds of discussion.

2.     Excel being a rich file format would have its own complexities to store the data. However, I am looking for a fix similar to “IMEX=1 ” if it exists for this case

 

Solution

Just select the rows to be deleted and rather than clicking the delete key on ur keybord, goto the rowheader in the selected section -> right click-> delete.

Actually what happens when you just do a simple delete the cell has data which is blank. Same as NULL and no value are different.

When you right click and then delete a row then the row is actually deleted and SSIS will not pick up that row.

Another option is to modify your package to ignore those rows by using ISNULL functions inside a Conditional Split within your Data Flow.  For those rows where one of the columns that you expect an actual value in ISNULL, then you direct that row to a “deleted row” output, which you don’t use.

SSIS: Continue a Foreach loop after an error

Problem

I have a SQL Server Integration Services (SSIS) package with a Foreach Loop container. Inside that container I have a task that sometimes can fail. If it fails, the loop should just continue, skip the current step and go to the next iteration. Is this possible in SSIS?

Solution
  1. setup Event Details – OnError
  2. set the system variable Propagate
  3. set the precedence constraint to completion or success based on your requirement

This tip will describe how we can implement such error handling in a Foreach loop within a SQL Server Integration Services Package. Two solutions will be presented: one using the ForceExecutionResult and MaximumErrorCount properties and one using the Propagate system variable.

SSIS Package Test Set-up

In this tip we’ll use a simple package with the following control flow:

Test Package

The Foreach container loops over a fixed set of numbers, using the Foreach Item Enumerator.

Loop configuration

At each iteration, the current value will be written to an integer variable. In the first Execute SQL Task, that variable will be used as a parameter in the following SQL statement:

DECLARE @denom INT = ?;
SELECT 1 / @denom;
WAITFOR DELAY '00:00:05'; -- wait 5 seconds so looping is better visible

The task will wait 5 seconds in each iteration, so that the looping is more apparent while debugging the package in Visual Studio. As you may have noticed, the third item of the set is the number zero, which will make the SQL statement fail with a divide by zero error. The goal of this tip is to make sure that the loop will do all 6 iterations of the loop.

The last Execute SQL Task is just a dummy task that doesn’t really do anything. It is connected to the first Execute SQL Task with an OnFailure constraint. This is done to study the effects of the solutions were going to implement in this tip.

When the package is executed without any changes, the first task will fail and the second task will be executed:

Default behaviour

Notice that also the Foreach loop container fails (and the package as well), despite all tasks and containers having the propertiesFailPackageOnFailure and FailParentOnFailure are set to False. These properties don’t seem to have any effect at all, so we won’t bother with them in this tip.

ForceExecutionResult and MaximumErrorCount Options in SSIS

Let’s start the first solution by setting the task property ForceExecutionResult to Success.

Setting the property

This property simply tells the task to succeed, no matter what it encounters. When we run the package, we get the following result:

Success?

The task itself didn’t fail, but everything else still fails. The Foreach loop container did not continue the loop as we wanted. To figure out why, we need to take a look at the logging.

Too much errors...

There we can clearly see the container and the package failed because the maximum amount of errors was reached (even though the property FailParentOnFailure is set to false everywhere). This is because errors are propagated to higher levels in the package, which we’ll examine in more detail in the next section.

The default value of the MaximumErrorCount property is 1. If we change this property on the Foreach loop container to 0 – which basically means to ignore all errors – the following result is achieved:

Success! Or is it?

In the logging we can clearly see that all iterations were performed.

6 iterations were done

However, the package still fails because the maximum amount of errors was reached. To avoid failure all together, theMaximumErrorCount on the package should also be changed.

Using the combination of ForceExecutionResult and MaximumErrorCount we can continue the loop when an error occurs. However, this makes the package and the container insensitive to other errors, which is not an ideal scenario. Arguably, you don’t even need the ForceExecutionResult property, you can just set MaximumErrorCount to 0 everywhere, but that’s not a good idea when it comes to decent error handling. Also notice that if you set ForceExecutionResult to Success, the OnFailure path is never called and the second Execute SQL Task is never executed.

The Propagate Variable in Integration Services

The second solution is a far more elegant solution to deal with errors in a loop. The problem with the first solution is that errors “bubble up” from the failing task to the higher levels (containers) right until the package level. When you check out the logging of SSIS packages, it’s possible that you see the same error message for each level in the package. This is because the error propagates through each level and each time a log message is sent. However, the propagation of the error can be stopped at the task level.

To do this, you need to add an error event handler to the first Execute SQL Task. You can do this by selecting the task and by going to the event handlers tab.

The event handlers tab

Click on the link to create the event handler. You can keep it empty. Go to the Variables pane and click on the Grid Options.

Selecting grid options in the variables pane

In the dialog, enable the system variables.

Enabling system variables

Look for the Propagate variable and set its value to False.

Disabling error propagation

This will stop errors from bubbling up to higher levels in the package. As you can see, the container and the package succeed, while the first Execute SQL Task fails and the second task is executed.

Success at last

When we look at the logging, we can verify all iterations were executed.

Success at last - logging

The third iteration still failed and an error is logged, it just didn’t crash the rest of the package.

Note that you could also put the second Execute SQL Task in the event handler, instead of using it in the control flow with the OnFailure constraint.

 

SSIS: SSIS Package failure – Environment reference Id: NULL. Description: The parameter ‘ReportDate’ does not exist

Error message:

Executed as user: red\mm.ahAgent. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 17:10:59 Failed to execute IS server package because of error 0x80131904. Server:server01, Package path: \SSISDB\TTT\packageReports\02c package.dtsx, Environment reference Id: NULL. Description: The parameter ‘ReportDate’ does not exist or you do not have sufficient permissions. Source: .Net SqlClient Data Provider Started: 17:10:59 Finished: 17:10:59 Elapsed: 0.296 seconds. The package execution failed. The step failed.

 

Solution:

The reason for this is that the existing job step that executes the package contains a /ENVREFERENCE switch in the command line, and that value must be updated for the new version of the package.

This is not obvious, as using SSMS to view the job step doesn’t display this switch anywhere. The only way that I have found to fix this is to script out a drop and create of the job, obtain the correct reference_id using code like this:

SELECT  reference_id
  FROM  SSISDB.[catalog].environment_references er
        JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
 WHERE  er.environment_name = @Environment
   AND  p.name              = @ProjectName;

and then updating the @command parameter of the call to sp_add_jobstep with the new number.

Helpful references (that you won’t find by searching for the error message):

Old post but to help others and might be a solution: When the SSIS Package is executed from a job it’s going to use the references set on the SSIS Project catalog.

When deploying a SSIS project again this references are lost and you need to reset them in the SSIS Catalog.

In SSMS > Connect the instance > open tree Integration Services Catalogs > SSIDB > Projects > Right click the project containing the Package executed in the SQL Agent Job > Click Configure > Click References

Is the reference still there? If not add it again. You can also script this as a post deployment sqlscript..

 

SSIS: Email error message details

What the steps below do is to handle error handling for your SSIS packages. And then within the error handling enable it so that you can email the exact error to email addresses so that they are notified when there is an error and what the error was.

 

Example:

  • In our example we are going to be using a package named: SSAS-DroppingPartitions
  • This is what will be referenced below.

 

Enabling Error Handling in an SSIS Package

  1. Open your SSIS Package
  2. Then click on Event Handlers at the top.
  3. Then click on:
  4. Click here to create an ‘On Error’ even handler for executable ‘Package Name’
  5. EG
  6. Click here to create an ‘On Error’ even handler for executable ‘SSAS-DroppingPartitions’
  7. Now you will see that your package should have the following at the top.
  8. Now your package is ready for specific event Handlers to be added.
  9. Below is what it will look like with the Event Handler Enabled

Enabling your package error to be emailed

Adding an SMTP – Connection Manager for the Send Mail Task

  1. The first thing that you need to do is to setup a new connection which will enable you to send the actual emails by doing the following below.
  2. Right click in the Connection Managers window and select the following:
  3. New Connection.
  4. Then click on:
  5. SMTP Connection manager for the Send Mail task
  6. Then click Add
  7. This will then open the SMTP Connection Manager Editor Window and you will need to fill in the following:
  8. Name:
  9. SMTP-Mail.Mailserver.com
  10. NOTE: This is the name of our mail server which we are sending the email.
  11. Description:
  12. SMTP-Mail.Mailserver.com
  13. SMTP Server
  14. Mail.Mailserver.com
  15. NOTE:
  16. This is the actual DNS address for your SMTP Server
  17. Tick
  18. Use Windows authentication.
  19. NOTE:
  20. This is because in our current setup the mail server requires Windows Authentication in order to send out emails.
  21. Then click Ok.
  22. Now you will see your SMTP connection in your connection manager’s window.

 

Adding the Send Mail Task and configuring the Send Mail Task

  1. What we are going to do is to configure the send email task to send email to the people who require the email. And then configure what gets send out as part of the email.
  2. Click in the Toolbox and drag the Send Email task into your Event Handler Window
  3. Right click on the Send Mail Task and click on Edit
  4. Under General put in the following for the Name and Description
  5. Send email if Task Fails
  6. Then click on Mail in the left hand side.
  7. Where it says SmtpConnection click on the drop down and select the SMTP connection that you created in the section called Adding an SMTP – Connection Manager for the Send Mail Task above.
  8. EG:
  9. SMTP-Mail.Mailserver.com
  10. Where it says From:
  11. This must be a valid FROM address that the mail server receiving the required email to relay will accept.
  12. EG:
  13. user@domain.com
  14. NOTE: In some mail server setups you will have to ensure that the from email address is allowed to replay via your mail server.
  15. Where it says To:
  16. This is to whom you want to send the emails to.
  17. usertoemail@domain.com
  18. Where it says Subject I put in the following:

                                                            iii.      SSIS Error: Package Name

  1. EG:
  2. SSIS Error: SSAS-DroppingPartitions
  3. Where it says MessageSourceType this must be left as the default which is:
  4. Direct Input
  5. We will configure the Message Source in the next step.
  6. So once complete for now it will look like the following:
  7. Next is where we are going to dynamically input our error message and details per SSIS package using System Variables and expressions.
  8. To configure the Message Source type click on the Expressions in the left hand side.
  9. Click on the plus sign next to Expressions
  10. Then click on the Ellipses button, this will open the Property Expressions Editor
  11. Click under Properties and click on the drop down button.
  12. Select the following:
  13. Message Source

                                                            iii.      Then once again click on the Ellipses button.

  1. This will open the Expression Builder
  2. Now this is where you will add the following into your Expression builder, an explanation will be below.

“Package:                              “+ (DT_WSTR, 50)  @[System::PackageName] +”.

Time:                                      ” + (DT_WSTR, 50) @[System::StartTime]  +”.

Task:                                       “+  (DT_WSTR, 50) @[System::SourceName]  +”.

Error Description:                ” + (DT_STR, 2000,1252)  @[System::ErrorDescription]

  1. NOTE: If you want to format your text, use Notepad and then copy and paste it from notepad into your Expression Window.
  2. What the above does the following:
  3. It starts with the Package name
  4. And then the package Name variable.
  5. Then it is the time of the error
  6. And then the StartTime variable.

                                                            iii.      Next is the Task Name

  1. With the TaskName Variable
  2. Finally is the Error Description
  3. With the ErrorDescription variable
  4. NOTE: Because the error is text you have to change the data type from the default which is DT_WSTR to DT_STR
  5. With this you then need to add the length and then also the code page
  6. In our example which works is the code page 1252
  7. Then click on Evaluate Expression
  8. This should then come back with the expression in the Expression Value window above theEvaluate Expression button.
  9. Below is what the Expression looks like

                                                            iii.     

  1. Click Ok.
  2. Now to add an expression for our subject click on Property drop down in the Property Expressions Editor.
  3. Select Subject
  4. Then once again click on the Ellipses button.
  5. This will open the Expression Builder

                                                            iii.      Now this is where you will add the following into your Expression builder, an explanation will be below.

“SSIS Error: ” +  (DT_STR, 50,1252)  @[System::PackageName]

  1. What this does is just takes the Package Name and puts it into the subject line in our email.
  2. Then click on Evaluate Expression
  3. This should then come back with the expression in the Expression Value window above theEvaluate Expression button.
  4. As shown below is what it looks like

                                                            iii.     

  1. Then click Ok 3 times to get out of the Send Mail Task Editor.
  2. Once complete it will look like the following:

 

Testing your Send Mail Task

  1. The final step is to test to make sure that the Send Mail task works.
  2. NOTE: I would only do this for the first Send Mail task, because if this works it should work for all other packages as well.
  3. Go into your Control Flow and deliberately change a task so that it fails.
  4. NOTE: In our example I changed an Execute SQL task, and then change the Stored Procedure name so that I knew it would fail because the Stored Procedure does not exist.
  5. Once you have saved your change then run the package in Debug mode.
  6. When the package runs it will FAIL which is what we want.
  7. Now if you go and look in the Event Handlers Window you should see your Send email if Task Fails as green.
  8. You should also receive the email with the error as you configured above.

SSIS: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

Scenario:

I get this error message when I am trying to execute SSIS package using stored procedure:

Declare @ExecutionID bigint, @SSISProcessRequestID int
WHILE EXISTS (SELECT 1 FROM SSISProcessRequest WHERE Processed = 0)
SELECT TOP 1 @ExecutionID = ExecutionID FROM SSISProcessRequest WHERE Processed = 0 ORDER BY DateRequested;
EXEC [SSISDB].[catalog].[start_execution] @ExecutionID;
UPDATE SSISProcessRequest SET Processed = 1 WHERE ExecutionID = @ExecutionID;

Solution:

There could be many reasons to get this error message:

  1. You do not have enough permission to run the SSIS package
  2. You do not have update, execute permission on the databases
  3. Your SSIS package protection level set to EncryptSensitiveWithUserKey and then deployed to the SSIS catalog and that is why it is giving error message

Discussion 1:

 

If your issue is with linked servers, you need to look at a few things.

First, your users need to have delegation enabled and if the only thing that’s changed, it’l likely they do. Otherwise you can uncheck the “Account is sensitive and cannot be delegated” checkbox is the user properties in AD.

Second, your service account(s) must be trusted for delegation. Since you recetly changed your service account I suspect this is the culprit. (http://technet.microsoft.com/en-us/library/cc739474(v=ws.10).aspx)

You mentioned that you might have some SPN issues, so be sure to set the SPN for both endpoints, otherwise you will not be able to see the delegation tab in AD. Also make sure you’re in advanced view in “Active Directory Users and Computers.”

If you still do not see the delegation tab, even after correcting your SPN, make sure your domain not in 2000 mode. If it is, you can “raise domain function level.”

At this point, you can now mark the account as trusted for delegation:

In the details pane, right-click the user you want to be trusted for delegation, and click Properties.

Click the Delegation tab, select the Account is trusted for delegation check box, and then click OK.

Finally you will also need to set all the machines as trusted for delegation.

Once you’ve done this, reconnect to your sql server and test your liked servers. They should work.

Discussion 2:

The problem is that integration services as of the 2012 release does not support credentials delegation which means even though the SSIs package is running under the right windows account it does not pass that account when it tries to access the database or file system as they say at the end of this topic.

 

This appears to be a authentication scheme problem. If my memory serves correctly, the appearance of the ANONYMOUS LOGON is indicative of NTLM being used instead of Kerberos. If your SQL Server service is running under a domain credential, you will need to ensure there is a Service Principle Name (SPN) present for SQL Server. The syntax of the SetSPN command can be found here: SetSPN In short, you must marry a protocol with a TCP Port and the service account. I believe the command will look something like this:

setspn -s MSSQLSvc/Server.Domain:TCPPort Domain\ServiceUser

To verify which authentication scheme your existing connections are using, run the following code:

SELECT
    dec.session_id,
    dec.auth_scheme
FROM sys.dm_exec_connections AS dec

If configured properly for Kerberos, you will see Kerberos in the auth_scheme column. If not, you will see NTLM.

Finally, if your setup requires multi-hop authentication, you will need to configure each subsequent hop before the SQL Server to allow delegation. Inside Active Directory, go to the properties page of the computer or user account that will need to pass through authentication to the SQL Server, go to the Delegation Tab, select “Trust this user for Delegation on Kerberos only”, and then select the service that this account will be passing authentication to (search for your SQL Server service account).

Domain account:

If the startup account/ User Account is in a Windows Server 2003 functional level domain, in active directory users and computers Right-click Startup account, and then click Propertiesclick the Delegation tab. select Trust this user for delegation to any service (Kerberos only).

If the startup account/User account is in a Windows Server 2000 functional level domain, in active directory users and computers Right-click Startup account, and then click , In the Account options box, confirm that Account is sensitive and cannot be delegated is not selected.

SSIS Error: Send email task error – Unable to relay for Masud_Ahmed@test.com

Things to consider:

 

Scenario

I had a SSIS package which included “Send Email Task”. I uploaded the SSIS package to SQL MSDB and ran as a SQL job and I was getting the following error message:

Message
Executed as user: SHELTER\SSIS_Admin. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 20:30:01 Error: 2015-12-01 20:30:02.07 Code: 0xC002F304 Source: WFT START EMail Send Mail Task Description: An error occurred with the following error message: “Unable to send to all recipients. System.Net.Mail.SmtpFailedRecipientException: Mailbox unavailable. The server response was: 5.7.1 Unable to relay for Masud_Ahmed@shelter.org.uk”. End Error Error: 2015-12-01 20:30:02.07 Code: 0xC002F304 Source: Send Mail Task Send Mail Task Description: An error occurred with the following error message: “Mailbox unavailable. The server response was: 5.7.1 Unable to relay for Luke_came@shelter.org.uk”. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 20:30:01 Finished: 20:30:02 Elapsed: 0.812 seconds. The package execution failed. The step failed.

 

Solution

The SMTP Server is not set up properly. This is an error from the SMTP Server and not SSIS error.

Basically, on SMTPRELAY server IIS was already setup to work as a smtp relay server still it was not working because on smtp IIS there was an access list of which server can use the smtp service and my server was not on that list. After adding my server on IIS–>SMTP–>Right click properties–>Access tab–>Add–>Enter ip address of the our server; it has worked.