Thursday, November 24, 2016

Is your Pre-Production database environment aligned with Production?

As database developer or BI developer you may get below questions many times:

1) Is our Pre-Production database has exact match with production database?
2) How do we ensure Test environment has good data quality?
3) Is our Test environment has big deviation than production system? and so on.

To answer those questions you  have different ways of investigating the database tables. However, one most obvious and first step could be counting rows of two different databse environemnts. You may think it's going to take a lot hell of time to count all the tables's row count in two different environments and list them manually.

well, you don't need to do this task manually. There is a way where you can get row counts of all the tables in a database with small piece of code. 

Please run the below code to find number of rows for each table in the chosen database.

USE AdventureWorks2012
DB_NAME() AS Current_Database,
SCHEMA_NAME(A.schema_id) + '.' + A.Name as Table_Name, 
SUM(B.rows) AS Row_Count
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id

GROUP BY    A.schema_id, A.Name

I have used AdventureWorks2012 databse and result set looks like below:

Fig 1: result set of the query

At the same way you can find out  number of rows of the tables across  different database environments. Now you can place the result side by side in excel and easily compare the row numbers and number of tables between two different database environments. If you wish to make this automated then you can put this SQL code in a SSIS package and load the data after comparison.

Friday, November 4, 2016

Fix the SSIS Errror: AcquireConnection method call to the connection manager (error code 0xC0202009)

I was doing ETL to get data from staging area to Data warehouse and while runnig the data flow task it's shown the error :  The AcquireConnection method call to the connection manager [DatabseName] failed with error code 0xC0202009.

      Fig 1: Error code 0xC0202009

 Interestingly, the same SSIS package is running without error in Test and Production. After a little bit of interenet search as found, a few people mentioned about changing the Debug options from 64bit to 32Bit, It means you set Run64BitRuntime=False, as like below:

Fig 2: Change 64bitRuntime from debug options
However, it did not work for me, then tried a attribute called 'Delay validation'. You need to click the Data Flow Task's property and change the 'Delay Validation' from TRUE to FALSE.

Fig 3: Change Delay Validation Property

After changing the property you should able to run the package without error,