SSIS Package Design Solution from Normalized tables to De-normalized Data Mart Layer / SSIS Columns to Rows

I had a problem while building one package.
I had to read the columns

Contact Association	Contact Type	Device Type	Value
Business	Telephone	Cellphone	12312
Business	Telephone	landline	1231231
Business	Telephone	Fax	1342343
Home	Telephone	Cellphone	12312
Home	Telephone	landline	1231231
Home	Telephone	Fax	1342343

This has to be inserted in 

Some Junk Columns	Business Cell No	Business Tele No	Bus Fax	Home Cell No	Home Tele No	Home Fax
Junkies..	12312	1231231	1342343	12312	1231231	1342343

like this, there are 92 columns in my warehouse table.

I was little pissed off with the approach suggested in my design document.

Do lookup for business entities and store bla bla.. then Pivot..

Pivot is not only meant for transposing columns to rows. it was best while aggregating / any operation how you play in your excel.

here is my design.

Do the lookups and get all data in one set.

Distribute the required set of rows with Conditional split Component

Add a Derived Columns component for every conditional split flow, Rename the columns accordingly,

I could not provide my solution samples since it would not be in compliance. will try to mock the scenario and some dummy package and post later.

Generate GUID / Global Unique Identifer simple approach

DECLARE @CNT AS INT =1; 

CREATE TABLE #tmpguid   (      id UNIQUEIDENTIFIER   ) 
WHILE ( @CNT < 100 )   BEGIN       INSERT INTO #tmpguid                   (id)       VALUES      (Newid()) 
      SET @CNT = @CNT + 1   END 
SELECT * FROM   #tmpguid 

SSIS Web Service Task: 417 Expectation Failed

I had some bad time with SSIS web service task... our daily loads used to throw me an exception....

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The request failed with HTTP status 417: Expectation Failed..
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

I tried fighting with Fiddler, then it has thrown me some other Error like could not establish secure connection between the server..
at last Ghees Alias saved my life [http://blog.qburst.com/2013/05/configure-ssis-web-service-task-effectively/]

all we need to do is to add the below configuration in 

Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn. Here, you will find 2 files, namely DtsDebugHost.exe.config and DTExec.exe.config. You need to modify these two files. Open each file on Visual Studio. Copy-paste the following tags inside the configuration tag of each file.

   

     

   

The configuration files should now look like this:

in normal C# coding in any script we can use the below line before we call our web method..

System.Net.ServicePointManager.Expect100Continue = false; 

Enjoy, it has started working fine.. woooooooh...

SSIS Parent Package Variables configurations

I had a scenario for executing a package like a stored procedure with different parameters.
I did not want to create multiple copies of the package. so here's my approach.

1. Create Parameter Variable(s) in the Child Package [note here, the names should be consistent]
2. Create Parameter Variable(s) in the Parent Package(s) and assign / modify the values in Parent package itself, then simply use a Execute Package Task to execute the child package. nothing else to be configured here. Variable Names and parent package configuration by default takes care of the variable value assignment.
3. The End.

Made life easy.. 

Cheers

Vivek.

MS Support Script for Creating Missed Indexes in all Tables

PRINT 'Missing Indexes: ' 

PRINT 

'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 

'be seen if the index was created. This is a unitless number, and has meaning only relative '

PRINT 

'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '

PRINT 

'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT '' 

PRINT '-- Missing Indexes --' 

SELECT CONVERT (VARCHAR, Getdate(), 126)                              AS runtime 

       , 

       mig.index_group_handle, 

       mid.index_handle, 

       CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                 migs.user_seeks + migs.user_scans )) AS 

       improvement_measure, 

       'CREATE INDEX missing_index_' 

       + CONVERT (VARCHAR, mig.index_group_handle) 

       + '_' + CONVERT (VARCHAR, mid.index_handle) 

       + ' ON ' + mid.statement + ' (' 

       + Isnull (mid.equality_columns, '') + CASE WHEN mid.equality_columns IS 

       NOT NULL 

       AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 

       + Isnull (mid.inequality_columns, '') + ')' 

       + Isnull (' INCLUDE (' + mid.included_columns + ')', '')       AS 

       create_index_statement, 

       migs.*, 

       mid.database_id, 

       mid.[object_id] 

FROM   sys.dm_db_missing_index_groups mig 

       INNER JOIN sys.dm_db_missing_index_group_stats migs 

               ON migs.group_handle = mig.index_group_handle 

       INNER JOIN sys.dm_db_missing_index_details mid 

               ON mig.index_handle = mid.index_handle 

WHERE  CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                        migs.user_seeks + migs.user_scans )) > 

       10 

ORDER  BY migs.avg_total_user_cost * migs.avg_user_impact * ( 

                    migs.user_seeks + migs.user_scans ) DESC 

PRINT '' 

go 

How to Select the Best Women

SELECT
ThePerfectWoman

FROM   AllTheWomenInTheWorld

WHERE  HerInterests
LIKE Mine

      
AND HerMate IS
NULL

      
AND LikesToLive IN (
'Where',
'Ever',
'I',
'Want',
'to',
'live' )

GROUP  BY
CASE

         
WHEN EverMyFeelings>=Sad

                
THEN SheCanMakeMeHappy
ELSE IwillMakeHerHappy
* 2

        
END

HAVING Max(Attitude)
> Good

MS SQL CTE wonders Display 1 to 100 without Looping

I happened to solve a puzzle.
Display 1 to 100 in SSMS without using any loop or table or table variable or even cursor.

I gave up, then got the solution from requester.

WITH cte 

     AS (SELECT 1 Num          UNION ALL          SELECT num + 1          FROM   cte          WHERE  num < 100) SELECT * FROM   cte 


Always wonder with common table expressions. happy querying