SSIS: Split the file into error file upon error while inserting to the destinaltion table

The SSIS imports a flat file to the SQL table and upon error it writes out the error lines ( including the contents to the a new file e.g. file1_error.txt and inserts the error records ( account number and the row number in this case) to the error ruble in the database. This multiple tasks are possible by the Multicast step.

The script component: Script to update the rowcount is used to count the rows in the source file.




int _RowNumber = 2;

public override void Input0_ProcessInputRow(Input0Buffer Row)



{
 



 



Row.RowNumber = _RowNumber;

_RowNumber += 1;

}
 

SSIS error while project deployment from the local machine to the server.

While doing a SSIS project deployment on my staging server from my desktop I ran into the following error

"Please create a master key in the database or open the master key in the session before performing this operation, ( Microsoft SQL server, Error:15581)"

The staging SSISDB database was a restore copy of the production .

I tried to alter the master key with the pwd from the prod
USE [SSISDB]
OPEN master Key decryption by password ='password' --my prod password
ALTER Master Key ADD encryption by Service Master Key

But again got the following error -

Msg 15313, Level 16, State 1, Line 2
The key is not encrypted using the specified decryptor.
Msg 15581, Level 16, State 7, Line 3
Please create a master key in the database or open the master key in the session before performing this operation.

Tried to create a master key -
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

tried to drop the master key -
use ssisdb
GO
drop master key

Got the following error
Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'MS_Cert_Proj_1' is encrypted by it.


Solution :
I backed up the SSIS Key on prod from the following

use ssisdb
GO

BACKUP MASTER KEY TO FILE = 'v:\backupkeys\SSISDBKey1
    ENCRYPTION BY PASSWORD = 'password' 

Then I copied the key file over to the test server in v:\backupkeys_staging\SSISDBKey1
use ssisdb
GO
RESTORE MASTER KEY FROM FILE = 'v:\backupkeys_staging\SSISDBKey1'
    DECRYPTION BY PASSWORD = 'password'  --prod password used to backup the key
    ENCRYPTION BY PASSWORD = 'newpassword' 
    FORCE  

SSIS error: Drop faield for user

I was trying to drop a user from SSIDB database and I encountered the following error -

The database principal has granted or denied permission to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 27226)


I ran the following query to find the permission of the user I am trying to drop


use SSISDB
GO

SELECT TOP 1000 [object_type]

,[object_id]

,[principal_id]

,[permission_type]

,[is_deny]

,[grantor_id]

, 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR)

+ ', @object_id=' + CAST([object_id] AS VARCHAR)

+ ', @principal_id=' + CAST(principal_id AS VARCHAR)

+ ', @permission_type=' + CAST(permission_type AS VARCHAR) [execute this]

FROM [SSISDB].[catalog].[explicit_object_permissions]

WHERE principal_id = USER_ID('usertodrop')


Then copied the contents of the [execute this] column in a new query window and executed it against the SSISDB database which revokes the permission .
Then I was able to drop the user.