Thursday, May 29, 2014

Physical Data Modeling with System Architect work around solution for easy handling

100 % third party component / exe free solution. so, all you need is SSMS or any other SQL Editor.
I bet most of the physical data modelers would have come across enough happy moments with System Architect tool (SA).

so, when we build / convert physical data model, we would come across few challenges
1. Names of Tables, Columns, Relationship would not be in proper format
2. Constraints
3. Indexes
4. Maintaining Associative Relationships from LDM to PDM.

so, the solution is instead of modifying names, relationships & constraints in SA, we could do it simply in SQL Editor and reverse engineer back to SA.

here are few steps would involve to make this happen.

  • open LDM
  • Click Dictionary --> Create Data Model --> Physical Data Model
  • Open PDM
  • Click Tools-->DB Schema Generation
  • Choose SQL Server any version availabe as Target db --> Next
  • Give local path for storing SQL DDL.. etc and finish the wizard to generate DDL
  • open DDL in any Editor, modify table names, column names as per the requirement
  • check constraints for all entities
  • Execute the SQL in SSMS to ensure the script is bug free.
  • Reverse Engineer back in SA in order to replace PDM.

Wednesday, March 12, 2014

To Delete All Default constraints from all tables of one Database

SELECT 'Alter Talbe '+ SO.NAME +' Drop Constraint '+ So1.name
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
Join sys.objects so1 on so.id = so1.parent_object_id and so1.type_desc ='DEFAULT_CONSTRAINT'
WHERE SM.text ='(newid())'
ORDER BY SO.[name], SC.colid

Monday, February 10, 2014

PIVOT UNPIVOT HIERARCHY repetition of levels issue

I happened to solve one business case in my work.
here is my source table

Source
Name Parent_key  Child_key  Level
CEO 1 1 0
Director Finance 1 2 1
Director Marketting 1 3 1
Project Manager 2 21 3
Project Manager 3 31 3
Team Lead 21 211 4
Team Lead 21 212 4
Team Lead 21 213 4
Victim (me) 213 2131 5

I'd like to TSQL Query in order to get output as follows.

Expected Output
Name Level0 Level1 Level2 Level3 Level4 Level5
CEO 1 1 1 1 1 1
Director Finance 1 2 2 2 2 2
Director Marketting 1 3 3 3 3 3
Project Manager 1 2 21 21 21 21
Project Manager 1 3 31 31 31 31
Team Lead 1 2 21 211 211 211
Team Lead 1 2 21 212 212 212
Team Lead 1 2 21 213 213 213
Victim (me) 1 2 21 213 2131 2131

I got the output at different levels by using a CTE & Pivot, 

Name Level0 Level1 Level2 Level3 Level4 Level5
CEO 1          
Director Finance   2        
Director Marketting   3        
Project Manager     21      
Project Manager     31      
Team Lead       211    
Team Lead       212    
Team Lead       213    
Victim (me)         2131  

but I struggled a lot to repeat parent levels values to the top most parent. also duplicate the bottom most child to the rest of all levels.

hence ended up writing the output in a temporary table and keep updating it.. (though I hate it, I need to do it.)

if you have any better solution, kindly let me know?

Thursday, January 9, 2014

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.


Tuesday, December 31, 2013

Generate GUID / Global Unique Identifer simple approach

DECLARE @CNT AS INT =1;
CREATE TABLE #TMPGUID(ID UNIQUEIDENTIFIER)
WHILE ( @CNT<100 br=""> BEGIN
INSERT INTO #TMPGUID(ID) VALUES (NEWID())
SET @CNT = @CNT+1
END
SELECT * FROM #TMPGUID

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.