MSBI

CREATE PROCEDURE NUV @TEST NVARCHAR ( 100 ) AS BEGIN IF @TEST = 'DHINAKARAN' GOTO DHINAKARAN --JUMPS TO DHINAKARAN LABEL . IF @TEST = 'BHARATH' GOTO BHARATH -- JUMPS TO LABEL NAMED BHARATH IF @TEST = 'JOB' GOTO JOB -- JUMPS TO LABEL NAMED JOB DHINAKARAN: --LABEL DECLARATION AND ITS FOLLOWING COMMAND PRINT 'DHINAKARAN IS A ASSOCIATE ETL DEVELOPER  AT NUVENTO SYSTEMS PVT LTD JOINED IN 2011, NOVEMBER' GOTO FINISH -- THIS IS USED TO PREVENT LABEL BHARATH WHICH IS BELOW FROM EXECUTING BHARATH:  PRINT 'BHARATH IS A SNR ETL DEVELOPER AT NUVENTO SYSTEMS JOINED IN 2009, MARCH' GOTO FINISH -- THIS IS USED TO PREVENT LABEL JOB WHICH IS BELOW FROM EXECUTING JOB: PRINT 'JOB IS A ETL DEVELOPER AT  NUVENTO SYSTEMS JOINED IN 2010, JULY' GOTO FINISH FINISH: END After running this stored procedure, if we try to execute with either of the name mentioned above like bharath, dhinakaran, job, you will get printed version which is mentioned in ...

Here is my table named P. Table contains information about some random people who is having different type of telephonic medium to be contacted. Here is how my table P looks like: But I need the output in following way as shown in the snippet Here we can see that Row value of Table P is mirrored as column name and Corresponding values are loaded as its row. This type output is very to analyze than original table P. Isn't right? Well To achieve this format of output, we use, PIVOT command in SQL. Here is the script and out of it in a Snippet: SELECT NAME , HOME , OFFICE , MOBILE FROM ( SELECT * FROM P ) T PIVOT ( MAX ( PHONE_NUMBER ) FOR PHONE_TYPE IN ( HOME , OFFICE , MOBILE )) OUTPUT1 ORDER BY NAME Here is how it works. The first two lines of the query is used to select what we need in the output. Next PIVOT command is used to change rows to column names where we are going to use some MultiValue func...

  Here is a example to create a stored procedure for finding number is perfect square or not. CREATE PROCEDURE SQR ( @NUM int ) --syntax for creating procedure and assigning a variable to accept some number. as begin -- Stored procedure shud start with AS BEGIN and should end with END command. DECLARE @RESULT FLOAT SET @RESULT = 0 DECLARE @DIV INT SET @DIV = 0 WHILE ( @DIV != @NUM ) BEGIN SET @RESULT = SQRT ( @NUM ) IF ( @RESULT = @DIV ) BEGIN BREAK --- BREAK COMMAND IS USED TO BREAK THE WHILE LOOP ONCE CONDITION ABOVE BECOMES TRUE. END ELSE SET @DIV = @DIV + 1 END IF ( @RESULT = @DIV ) BEGIN PRINT ( 'THE NUMBER IS SQUARE' ) END ELSE PRINT ( 'THE NUMBER IS NOT SQUARE' ) END ------------------------------------------------------------- After running the above stored Procedure, We can check the output by using EXEC SQR 81  where we can change value of 81 as we wish to do.

When I wanted to know how to remove duplicates or in more sense, If i wanted to get Unique records, I went through a post which suggested to go for Rank function to find unique records.  Well some suggested to for Row_number function. Here is a example where I worked on both the types and let you the differences. CREATE TABLE Table1 ( ID INT , employee VARCHAR ( 100 ), sal INT , dept VARCHAR ( 100 )) GO INSERT INTO Table1 SELECT 1 , 'dhinakaran' , 100 , 'finance' UNION ALL SELECT 2 , 'bharath' , 100 , 'marketing' UNION ALL SELECT 3 , 'job' , 100 , 'accounts' UNION ALL SELECT 4 , 'job' , 100 , 'markting' UNION ALL SELECT 5 , 'job' , 200 , 'infrastruture' UNION ALL SELECT 6 , 'raghu' , 300 , 'markting' UNION ALL SELECT 7 , 'raghu' , 400 , 'finance' GO SELECT employee , sal , dept FROM ...

IF... ELSE statement can be used writing T-SQL. But when It comes to SSIS, we can assign the condition using DERIVED COLUMN TRANSFORMATION to achieve this. Here I'm having a condition, That if one of column in a table is having "US", Then I need to update other column as "YES" and if its not "US", then I need to update it as "NO". So for this situation, I need to have IF ELSE statement but I'm using DERIVED COLUMN Tx for this purpose. Condition I'm quoting is as follows: COUNTRY == "US" ? "YES" : "NO" . This means, When Country column is US, then YES, else NO.. In DERIVED COLUMN Tx, we can mention column name where we need to pass this YES and NO at the place derived column name slot and if we need to replace other column also, we have a option called derived column.

Here is a post where I actually did to pass variables into SQL query using Execute SQL task. Here is my condition that I need to count number of rows in one table and need to pass this count value into a variable and then  using that variable I need to insert into a table. For this Purpose let me take a table named 'Dim' under Database 'test' and similarly I will take one more table called 'fact' under database name 'test' again. Using SSIS, I will take Execute SQL task on workspace, In fact two as we need two executions has to be done. Let us create a variable as we discussed earlier of this task purpose and I'm creating a variable called "testing". Right click on first Execute SQL task, go for edit and here, I will configure as shown in snippet below. Here importantly we need to set ResultSet as Single row as ResultSet option will activate Result Set tab of Execute process t...

We have many scenario that we need to Zip many files which we come across and then so some operations like either sending it as a email or just moving zipped file to some other destinations etc. But we were using manual method to zip multiple files. In this post, I tried to create a package which will zip multiple files using SSIS. Here for Zipping files purpose, I'm using 7-ZIP which is free software available in google sites. Download files and install onto your system. First let me show how to Zip on file and later I will show how to zip multiple files using SSIS and 7Zip tool. Compressing Single file. Here I'm trying to Zip one single flat file which is of 40MB size. I kept this file in C:\Documents and Settings\\Desktop\test\source folder. Now to compress this file, I will open my SSIS and I'm dragging and dropping EXECUTE PROCESS TASK from Control Flow. Now right click on Execute Process task and go for edit and select Process option. In process tab, ...