Tuesday, October 29, 2013

ActiveX Script Task in SSIS 2005 and 2008

ActiveX Script Task in SSIS requires the following library to be registered manually using regsvr32.exe on the Windows OS that runs SQL Server.

Otherwise, the SSIS package may fail when it is run as a SQL Server job.

[SQL Server 2005]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\90\COM\AXSCPHST90.DLL"

[SQL Server 2008]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\100\COM\AXSCPHST.DLL"

Another element of surprise to keep in mind is to try configuring the SSIS package to run in 32-bit mode. I realize that SSIS Task such as Excel Destination would only work when run in 32-bit mode. 

When you create a SQL 2008 SSIS package on 64-bit Windows 7, for example, the SSIS package defaults to 64-bit environment (as well as .NET Framework 2.0). You can change these default settings in the property section of either at the Task-scope or project-scope.

Of course, if you choose to use 32-bit environment when you complete your SSIS package, you'd have to run the SQL Server job that runs the SSIS package in 32-bit mode as well. You can set this option in the job properties window.

Monday, October 28, 2013

Convert a DataTable Column into String Array

Suppose there is a single column DataTable with user names. In order to convert the data in this column into a string array you can do it in for-loop and LINQ approaches.

For-Loop approach:

int columnIndex = 0; //single-column DataTable
string[] userArr = new string[dt.Rows.Count];
for( int i = 0; i < dt.Rows.Count; i++)
    userArr[i] = dt.Rows[i][columnIndex].ToString();

LINQ approach 1:

int columnIndex = 0; // single-column DataTable
var userArr = dt.Rows.Cast()
                     .Select(row => row[columnIndex].ToString())

LINQ approach 2:

int columnIndex = 0; // single-column DataTable
List userList = new List();
foreach( DataRow row in dt.Rows )
    userList.Add( row[columnIndex].ToString() );

How to check CentOS version

In order to check current version of CentOS, try the following.

>  cat /etc/*release*

You will see the version similar to the following.

CentOS release 6.4 (Final)
cat: /etc/lsb-release.d: Is a directory
CentOS release 6.4 (Final)
CentOS release 6.4 (Final)

Or more specifically...

cat /etc/centos-release

This will show just the version number as follows.

CentOS release 6.4 (Final)

Thursday, October 24, 2013

Cannot find server certificate with thumbprint error ... TDE error


  1. Primary SQL Server's database has TDE (Transparent Data Encryption) enabled and encrypted with Master key (private key). 
  2. The database backup has been made and backup file was copied to a secondary SQL Server. 
  3. When restoring the database on a secondary SQL Server, "Cannot find server certificate with thumbprint error" happens.

1. Backup the certificate with master key on the Primary Server
  • BACKUP CERTIFICATE  [EncryptionCertificate]
    TO FILE = 'Certificate File path'
    WITH PRIVATE KEY (FILE = 'Master Key File path.dat', ENCRYPTION BY PASSWORD ='password')

2. Restore the certificate with master key password on the Secondary Server
  • CREATE CERTIFICATE [EncryptionCertificate]
    FROM FILE='Certificate File path'
    WITH PRIVATE KEY ( FILE =  'Master Key File path.dat' , DECRYPTION BY PASSWORD ='password')

To find a file in Linux by name

For example, to find a file "pg_hba.conf" in CentOS

Method 1
>  cd /
>  sudo find . -depth | grep -i pg_hba.conf

Method 2
> cd /
>  sudo updatedb
>  sudo locate pg_hba.conf

To check if postgresql is running

To check if PostgreSQL service is running on Linux on CentOS:

/sbin/service postgresql status


/sbin/init.d/postgresql status


ps | grep -i pgsql