Security of a package - SSIS

When you are designing a SSIS package, at times there will be several other developers need to work on the same package, but you need a setting that when you open the package you do not have to reenter the username and package, but when another user opens the package the system should ask for the authentication details.

How can you achieve this.

In the package click on properties --- protection level

The following options are available for selection

• Do Not Save Sensitive will remove sensitive data every time the package is reopened.

• The Encrypt Sensitive With User Key protection level encrypts the sensitive information in the package by using keys based on the current user. When a new user opens the package, he or she must reenter sensitive data, such as a SQL Server password.

• Encrypt Sensitive With Password will encrypt sensitive data with a package password. This would require you to enter a password every time you opened the package.

• Encrypt All With User Key will encrypt the entire package with the user key. A new user will not be able to view or execute the package.

Data Transfer slow - SSIS package

In a case where we are transferring data from a source to destination is slow using the data flow task in SSIS package, the following can be considered.

In the destination task --- oledb destination task , the OLE DB Destination, when connected to SQL Server, enables data to be inserted with bulk insert statements by using Fast Load, click on the olebd destination properties --- click on access mode --- select fast load

Error: 18456, Severity: 14, State: 16

Error: 18456, Severity: 14, State: 16


This error is caused based on the user password is not correct from the client system. This message appears in the log file in this location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

This error also appears on the event viewer.

Error: 18456, Severity: 14, State: 16
Login failed for user 'sa'. [CLIENT: 192.168.x.xxx]

If this message appears continously check with client system if there is any sql job running and trying to attempt to connect to the server and password is not set right.

In our case we had a diagnostic tool running from this client and the password was changed, this has caused this error message

SQL SSRS Report optimization

Filtering the dataset might be slower than using the parameters when you are not using a cached report.

Filtering the dataset enables you to use a cached report that reuses the same data.

Filtering the dataset means that SQL Server Reporting Services will query all the data and later filter the information. The advantage of filtering the dataset is that other reports can use the same data when using cache reports, increasing their performance. However, if the report is not configured as cached, filtering the dataset will likely be slower than using parameters.

Sql Connection

Error - 26 A network related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or not accessible. Verify the instance name is correct and the sql server is configured to allow remote connections

For the above please check the following.

Option 1
1. Log into sql server , go to start --- control panel ---administrative tools --- services
2. Check sql server and make sure it is running.
3. Check for sql browsing services and make sure it is running.

Option 2
1. go to start --- programs --- administrator tools --- computer management
2. Click on services and applications
3. Click on sql server 2005 network configurations
4. Click on protocols for MSSQL Server
5. Click on TCP/IP
5. click on IPAddresses tab , check for the ports to be 1433

Option 3


1. check the main  firewall , please make sure you contact your network admin when you do this.
2. Under the change settings
3. Go to the exception tab
4. add port
5. Name : SQLPort Number: 1433Protocol: Select TCP

We had the same issue again on 20th July 2012 where Brian had a condition to allow the ports, this was removed by Brian in the main firewall and it worked.

SQL Server Profiler results

To check the sql server profiler results in case of monitoring SSAA the following are the ways to measure.

1. The integer data displays the numerical information associated with the events.

2. The text data column shows the text description of the event such as MDX statement

3. The EventSubclass column shows the event subclass, such as the ExecuteSQL, WriteData, BuildIndex, or other subclass.

4. The duration displays the event duration.

SQL Server Names Instance installation

SQL Server Named Instance --- Connection issue

I have created a second instance in my sql server as MSSQL2, after the installation I tried to connect using the SSMS sql server management studio, but to my surprise i was given a error to check if the sql server exists or user details are correct.

The first instance connected without issues. But the second instance MSSQL2 will not connect.
I did check my authentication details, username and password.
I did check the sql surface area configureation details to see the MSSQL2 is online.
I did check the services to see if the MSQL2 is online.

But the issue was the sql server browser service was NOT online. When I brought is online i was able to connect without issues.

SQL error handling,begin Tran,Commit , rollback, try and catch

BEGIN TRAN
s--sql statment
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO

SQL DB Mail

--db mail
EXEC msdb.dbo.sp_send_dbmail @recipients = 'vijay@xxx.ie',
@copy_recipients = 'vijay@xxx.ie',
@subject = 'LMK GP Integration Failure - Receivables',
@body = 'LMK GP Integration Failure - Receivables';

SQL SSAS High Availability

High availability and maximum uptime of ssas cubes, to achieve this we can use Microsoft clustering services (MSCS)

The following are the major advantages of doing so.

1. SSAS Solution will stay online even the operting system is corrupted.

2. SSAS Solution will stay online even when there is a hardware failure.

SQL Database Backup

To run this please have database options recovery model set to FULL, this can be changed in the SSMS --- right click databse --- properties ---options --- recovery model to FULL

--******************************************************************
--schedule this for nightly run
--****************************************************************************
--back up database
BACKUP DATABASE [Database] TO DISK = N'Z:\DATABASE\Database_backup.bak' WITH NOFORMAT, INIT, NAME = N'ManufacturingChina_backup', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
--backup up transaction log
BACKUP LOG [Database] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Database.trn' WITH NOFORMAT, INIT, NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
-- shrink log file
DBCC SHRINKFILE (N'Database_Log' , 786)
GO
--******************************************************************
--schedule this for hourly run
--****************************************************************************
--backup log hourly
BACKUP LOG [Database] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Database.trn' WITH NOFORMAT, NOINIT, NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- backup log hourly
DBCC SHRINKFILE (N'Database_Log' , 786)
go

SQL Data Mining - Processing

Process clear structure --- removes the training structure withou affecting the mining model.
Process Structure --- loads the data to the structure without affect the mining model.
UnProcess --- removes the data from structure and the mining models
Process Full --- process both the mining and the structure.

Data Mining

Data mining is generally described as data exploration to find patterns that can be useful for the business organization. It helps you understand a predictive behaviour, identify relationships and group items like customers, products etc., for example our brain is a data mining tool, where the data is stored as we accumulate through various experiences we face through our life. This data is then used to identify certain predictive patterns to be used to the future.

Microsoft has put forth algorithims offered in sql server 2005. the models are
i. decision trees
ii. naive baiyes
iii clustering
iv sequence clustering
v time series
vi association
vii neutral network

Decision Tree : Lets take example of a decision to identify couples who are likely to form a successful marriage. The input attributes will include age, religion, gender, political views, height and so on. the predictable attribute will be marrigage outcome. The first split say is based on the political views 70-30, and the second split is based on the height. In the 70% split, the second split say is height split as 60 are 6' tall and 40 are less than 5' 8" and on the 30 % split, the second split say is 58% are 6' tall and 18% are so less than 5' and 8", this makes to predict the chances of having successful marriage are sharing 70% common political views and men who are 6' tall.

Naive Bayes: This is model can be used to predict a income range for the given occupation.

Clustering: This model can be used to predict where a graph showing per capital income vs per capital dept for each country. This helps to read where there is huge number of countries, by clustering them together it is easy to read.

Time series: This model can be used to predict based on the given time. For time series algorithim you need key time, input and predictable attributes.

In my next article let us go through a step by step process of creating a data mining project.

Get Accperiod / Financial period

--Function to get account period for example if the financial month of the company is starting from 01st July to 30th June and you would like to know the financial period this funtion will provide the financial period based on the given date.

For example 01st July 2009 will 01/2009 and 01st Nov 2009 will be 05/2009

ALTER Function [dbo].[GetAccperiod](@transdate as datetime)
returns varchar(10)
as
begin
declare @AccPeriod varchar(10)
declare @Month smallint
declare @Year int
--declare @tod datetime
--select @tod=cast(@transdate as datetime)
begin
--Get Current Month
select @TransDate = convert(datetime,convert(varchar,@TransDate,101))
select @Month = month(Closedt),@Year = Year(CloseDt) from tblOpenMonth where opendt <= @TransDate and CloseDt >= @TransDate
-- Select @Year = year(@transdate)
if (@Month < 7)
begin
Select @Year = @Year -1
end
if (@Month >=7)
begin
Select @Month =@Month -6
--Append zero if month returns a single digit value
if @Month < 10
Select @AccPeriod='0' + cast((@Month) as varchar(2))
else
Select @AccPeriod=cast((@Month) as varchar(2))
end
else
begin
Select @Month = @Month + 6
--Append zero if month returns a single digit value
if @Month < 10
Select @AccPeriod='0' + cast((@Month) as varchar(2))
else
Select @AccPeriod=cast((@Month) as varchar(2))
end
Select @AccPeriod = @AccPeriod + '/' + cast(@Year as varchar(4))
return (@AccPeriod)
end
End

Removing the witness server

In the database mirroring, if the database has been configured with the witness server, there are certain advantages and certain disadvantages.

The advantages includes when the principal server fails, the mirror automatically takes over, please as soon as the sql service in the principal server fails.

The disadvantage if you want to restart the principal server even at that point the mirror server takes over, then to bring this back again, we need to login to the mirror server and click on the failover.

To remove the witness server from database mirroring after it has been configured with witness server adopt the following steps.

Please note before doing this, this needs to planned off the working times, also there should be complete backups and log backups done before this.

1. Stop the mirroring on the principal server.

2. Take a log back up.

3. Then configure again only with principal and mirror server.