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;

}
 

Keeping the size of the SSISDB to minimum

The  SSISDB database size was growing large in the SQL server 2012 machine.  I had to clean the SSISDB to  bring the size to minimum.

The SSIS catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the SSIS server. When the SSIS catalog is implemented, the SSISDB database is automatically created.
SSIS projects, packages, parameters, environments, and operational history are all stored in the SSISDB database so if you have hundreds of SSIS packages or packages that run every few minutes you could see how the database storing all the historical information would grow exponentially.

Steps I took to reduce the size of SSISDB

Checked that the SQL maintenance job ( created by SSIS Installation) was running, the job name is SSIS Server Maintenance Job

Looking at the first step, SSIS Server Operations Records Maintenance, I notices that it executes a stored procedure named internal.cleanup_server_retention_window.  This was the stored procedure that cleans up history

I could also   see that the stored procedure queries catalog.catalog_properties to find these values.

use SSISDB




GO
  

 

I changed the property value of the property name Retention_window to 10 days from 365 ( I could update to this short time frame as this was a dev server)

update c

set property_value = 100

from catalog.catalog_properties c

where property_name = 'RETENTION_WINDOW'

 

Then I ran the maintenance job again and the database size reduced.

 

I found all this information  in the following link-

 

https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/ 

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  

Using Lag to get the difference between two rows

I used the following query to find how name rows were there in a table in a given date where the time captured in a column were more than one minute between two consecutive rows

--using lag
  declare @startdate date ,@enddate date

set @startdate = '01/09/2017'

set @enddate = '01/09/2017'




 SELECT *

FROM (

SELECT LogId, lag(PackageStep) over (order by packagename) Over1minPackagestep,

LAG(starttime) OVER (ORDER BY starttime)

StepStartTime ,starttime as StepEndTime, HospitalCode

FROM Package_RunHistory where cast(starttime as date) between @startdate and @enddate

) q

WHERE StepStartTime IS NOT NULL

and DATEDIFF(minute, StepStartTime, StepEndTime) > 1

and clientcode is not null



　

Creating Excel File using SSIS

This process creates a excel file with data from the database.

In Execute SQL Task we create the table( excel sheet) . The columns are the column names returned by the query and the names appear as it is in the header of the sheet if we have checked the box where is says first row contains header.

 The following is the data flow task where the excel destination uses a dynamic file connection manager.

 

We are creating the ExcelFilepath property of the connection manager as follows. WE declare a user variable called @varExcelFileName and build the expression for it as follows:

@varExcelFileName  =

@[User::varOutFilePath]+ Replace(@[User::varReconFileName],".xls","") + "_" + (DT_WSTR, 4) YEAR( GETDATE()  ) +(DT_WSTR, 2) MONTH( GETDATE()  )+(DT_WSTR, 2) DAY( GETDATE()  ) + ".xls"

 

Find age based on date - TSQL

--this query finds the current ( as of the date the query is run) age of the person.

declare @dob datetime

set @dob = '07/12/1966'

select CASE

WHEN dateadd(year, datediff (year, @dob, GETDATE()),@dob) > GETDATE()

THEN datediff (year, @dob, GETDATE()) - 1

ELSE datediff (year, @dob, GETDATE())

END as Age

--select dateadd(year, datediff (year, @dob, GETDATE()),@dob)

--select datediff (year, @dob, GETDATE())

db_executor role for user permission to execute all procedures

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

 

--then add a member to this role