Monday, June 24, 2013

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:
img8


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...

Thursday, June 20, 2013

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.



Monday, June 10, 2013

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 

Wednesday, June 5, 2013

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