Surprise ! Visual Studio 2017

Isn’t it surprising ? Microsoft announced Visual studio 2017 RC on 16-Nov-2016.

You can get download the RC version from following link

Download Link

Now , your question will be What’s new in Visual studio 2017 ?

To know more about Visual Studio 2017 check the release note of Visual studio 2017 with following link

Release note Link

I am pretty much sure you will enjoy this latest release.

Please, share your thought for same.

RJ

Advertisements

Here Comes New Idea of Split String in SQL SERVER 2016 #3

In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task  in previous SQL SERVER versions either you will write a function which split the string and return desire values in a column  or

you will use XML function or  might be different custom functions.

Let me explain this with below example. Suppose you have a string like below

DECLARE @FriendList AS VARCHAR(1000)

SET @FriendList =’Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed’

 

Now you want output like below

output_of

 

Then in such cases, you will  follow 2 approaches (their might be other as well)

Approach 1:- Write  a function like below  and use it.

Traditional_way_Split_String_Indiandotnet

And once this function is created you can use like below

use_of_Split_Function_in_SQL_Indiandotnet

Approach 2 :- You can use XML option in SQL SERVER as  shown in below

String_split_by_XML_SQL_SERVER

So, the good news is now in SQL SERVER 2016 you don’t need to write  so many lines to split any string. In SQL SERVER 2016 a new string function is Introduced which is

STRING_SPLIT

The use of this function is very easy and below is the syntax

STRING_SPLIT (string, separator)

Now, let me show you same output using STRING_SPLIT function

string_Split_Function_sql_server_2016_Indiandotnet

Isn’t it easy ?

I hope you will like this easy way to split the string.

Provide your feedback.

RJ !!!

Compress & Decompress–new Feature in SQL SERVER 2016 #2

This is another article in the series of SQL SERVER 2016 Journey . I am pretty much sure you might aware of Gzip Compression algorithm. If not then try  this link.

 

So, SQL SERVER 2016 introduce this two awesome functions for Compress & Decompress the data.

Before SQL SERVER 2016 version we have data compression feature like Page & Row compression (check Previous post for it Link )which is different then this column value compression.

 

In SQL SERVER 2016 Compress function,  data compression is done via GZIP algorithm and return VARBINARY(MAX).

 

Below is the simple syntax of Compress function

 

Compress (Expression)

Here Expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n)

 

Decompress function is just opposite of  compress function. It is used to decompress the value of VARBINARY which is converted using Compress function. The only tweak is you need to cast the output of Decompress function  in specific data type to make it readable (if using varchar ,nvarchar compression) .

 

below is the simple syntax of Decompress
Decompress (Compressed string)

 

Let’s understand this via an example as shown below .

 

Indiandotnet_Compress_Decompress_Feature_SQL

In this example I have taken 3 tables with exact same schema & data

 

  1. 1) IndiandotnetFriends
  2. 2) IndiandotnetFriends_Compress
  3. 3) IndiandotneFriends_Decompress

 

You can see  snap in which we are inserting same data.

As the name suggested in first table normal data from Adventureworks’s person table.

In second table we are inserting compressed value of first Name  and in 3rd table we are inserting decompress value of First Name from the Compressed table.

Now, let’s check compress  & decompress table data

Check_Compressed_Decompress_Datais

 

Now, Your might thinking that the output of both compress and decompress is not readable.

So you are right to make data readable of Decompress table we need to type cast.

See below snap for same.

 

Decompressed_value

 

Till now we know how to use this Compress & Decompress function. Now, let me share the benefit of using Compress. if you see below snap you will find that data length of compress is comparatively less than normal and decompressed data length .

 

DataLength_Indiandotnet

 

Obviously, compression helps you somewhere in the overall performance of your application.

The good point is  you can pass the compress data to your .net application and decompress using GzipStream as well.

 

The only thing which we need to take care is type casting. Suppose your base column which compressed is VARCHAR then you need to typecast again in VARCHAR.

 

Now, next question is where we can use this functions. So,  we can use in compressing large object like binary data in which we save jpg, pdf , word document etc..

 

I hope you will be excited in using this function.

 

Please, share your input.
RJ!

DROP IF Exists- A cool feature in SQL SERVER 2016

In the Series of SQL SERVER 2016 journey, this is our new article. In this article, we are sharing a new cool feature which introduced in SQL SERVER 2016 which is DROP IF EXISTS (DIE) .

In our development many times it happens that we need to drop a table and as a best practice we write the following syntax as shown in below figure

TraditionalWay_Indiandotnet

Now, in SQL SERVER 2016 the same task is super easy. You can write the following syntax to drop the table object

DROP_TABLE_IF_EXISTS_INDIANDOTNET

DROP TABLE IF EXISTS TABLENAME

The best part is if suppose the object does not exist then  here will be no error execution will continue.

Let me share one more example of Dropping a stored procedure.

DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET 

Similar, way we can write for following data objects and with the following syntax

Procedure:-
DROP PROCEDURE IF EXISTS Procedure Name

Assembly:-
DROP ASSEMBLY IF EXISTS Assembly Name

ROLENAME :-
DROP ROLE IF EXISTS ROLENAME

TRIGGER :-
DROP TRIGGER IF EXISTS Trigger Name

VIEW:-
DROP VIEW IF EXISTS View Name

RULE:-
DROP RULE IF EXISTS RULENAME

Type:-
DROP TYPE IF EXISTS Type Name

Database:-
DROP DATABASE IF EXISTS Database Name

Schema:-
DROP SCHEMA IF EXISTS Schema Name

User:-
DROP USER IF EXISTS Username

SECURITY POLICY:-
DROP SECURITY POLICY IF EXISTS Policy Name

View :-
DROP VIEW IF EXISTS View Name

FUNCTION:-
DROP FUNCTION IF EXISTS Function Name

SEQUENCE:-
DROP SEQUENCE IF EXISTS Sequence Name

INDEX :-
DROP INDEX IF EXISTS Index Name ON Table Name

Synonym:-
DROP SYNONYM IF EXISTS Synonym Name

I like this feature I am sure you will also like this.

Please, do share your feedback for blog post.

Enjoy !!

Unbelievable SQL SERVER 2016 Feature you should aware.

 

Although, I know I am bit late to share this thing on our blog but it says in Indian proverb “Der aai durust aai” means it’s OK you came late but you came that is more important.

Anyways, so you all might aware that Microsoft launched SQL SERVER 2016 officially in June 2016.

You can download the SQL Server 2016 via Link.

Obviously, this is a new revolution in SQL SERVER series. You will find many great features in this version.

This post is beginning to explore all those great features and we will do deep dive in all those features. In this, post we briefly introducing those features. so, without wasting time let me share a brief introduction.

 

1) JSON in SQL SERVER :-

Is this surprising to you ? Obviously, yes. As you might aware that most of the NO SQL database use either JSON or XML. As XML feature already exists in SQL SERVER so this was time for JSON. You can play with JSON in SQL SERVER 2016.

 

2)Always Encrypted :-

If you talk about security this one the best feature. Now, you are thinking what it means. So, It means that the data in the SQL SERVER reside always in encrypted format and SQL server can perform the operation on the encrypted data without decrypting it. The encryption key can be exist in some other system. With this, feature you can secure you ensure that your data is secure from the person like DBA / Developer as well. These guys also can’t see the actual data. Isn’t it neat ?

 

3) Row Level Security :-

This is another interesting feature which helpful especially to the developers  who needs to write extra code to check this. Let me explain this with an example suppose you have a sales team who do market research and you want to restrict that each sales manager can see only those data which entered by him only in such cases you don’t need to write specific condition in your code. It can be achieved by Row Level Security.

 

4) “R” in SQL SERVER :-

For the data scientist, it is a great NEWS. As Resolution Analytics is purchased by Microsoft and It is incorporated in SQL SERVER. You can run R analysis query in SQL Server.

 

5) Temporal Table:-

The Temporal table holds the old version of ROWS of a table. It means that it maintain a copy of the old rows in the table whenever there is an update on the main table.

 

6) PolyBase :-

With the help of this feature, you can access data which exist in Azure Blob or Hadoop cluster using the same SQL server. In the nutshell, we can say this is the technology which combines both relational & non-relational database in a single umbrella which is SQL SERVER. You can run the query directly on external data like Hadoop or Azure blob storage.

 

7) Stretch Database:-

I am pretty much sure by the name you can guess this feature. So, with the Stretch database you have can store your part of the data in the cloud which depends upon your need. You can say most recent transactional data you can store in your local environment and other old data you can store in Azure.

 

8)Query store :-

Another interesting feature to help you in identifying  performance drag using Query store. When you enable this feature it automatically captures a history of queries , plans,  and statics and retain them for review and resolve the performance issues.

 

9) Mobile report:-

As mentioned earlier this is the revolution in SQL SERVER 2016. In SSRS there are many important changes introduced. Now we can import Power BI report in SSRS and apart from this you can create a mobile report which you can run on Mobile.

 

Now, we started officially SQL SERVER 2016 tutorial series.

 

Moving forward we are going to discuss all these features in details and also the couple of new things which introduced in SQL SERVER 2016.

 

enjoy.

Happy VijayDashmi.

Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data

Data1

Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.

DragDropMulticast

Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax

FileDestination

After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.

finalDesign

Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.

Final_Result

We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

Enjoy !!!

RJ !!

Step by Step SSIS–Derived Column Transformation TIP # 120

Dear Friends,

In the series of Step by Step SSIS , Zero to Hero in SSIS this is another post in which we will try to understand what is Derived Column Transformation.

Let’s understand this by a real world example. Suppose, we are a shop keeper and our work is buy things from carpenter and sell them by adding our 20%  margin.

So , carpenters give us source data (file, sql server etc) with their rate according to product. What we do actually ,we add another column in our register by adding 20% which is our selling price. So ,our selling price column is a derived column here which we derived from source by adding some of our operations.

Isn’t it simple ?

Let’s understand this now step by step. I am using Adventureworks2012 database here as a source database and using product & ProductCategory table combination query. So , Assume this source data is data of carpenter’s products.  Here if you see below image we have productId, Product Name, color,Category & ListPrice (price at which we (Shop Keeper bought the the product)

Data1

Step 1:- It is useless to say here add a new Package in your project. Drag drop data flow task on canvas. Now double click the data flow task you will get a new screen where you need to drag drop source Assistance.

Now,need to configure source assistance where database will be adveturework2012.  If you see below we have same columns which we shared earlier in figure

SourceColumn

Step 2:- I am sure you will not face any problem in configuration of source control. Now in step 2 we have to drag drop Derived column.  Once you drag drop derived column you need to provide output of source assistance to Derived Column Transformation. Now to configure this use context menu by right clicking and choose Edit option you will get below screen. Here we have different functions and operators which we can be utilized with columns and variables and parameters to create derived column.

DerivedColumnSettings

If you see above highlighted row in image we can add a new column or can replace any existing column as shown in drop down.

Step 3:- If you see there is expression column also where we can add custom expression. Here we are multiplying 0.20 in list price and adding it again in Listprice and aliasing this new column SellingRate as shown in below figure

Expression

if you see below screenshot we can add other functions ,operators also.

VariousOpeartors

Step 4:- I hope above steps are pretty much clear. Now add a destination file in which we will get derived column “SellingRate” . We have to configured the derived column. As shown in below figure we configured derived column.

SellingRatemapping

Step 5:- Now, Once the destination file is configured run the package by pressing F5 or clicking run option. If everything working fine then we will get all the green checkbox sign as shown below figure

FinalResult

I hope this post might help you to understand the derived column.

Enjoy !!!

RJ!!!

Step by Step SSIS – Conditional Split Transformation TIP #117

In the series of Zero to Hero in SSIS this is our next post. In this post we will see Conditional Split transformation.

I am pretty much sure that by the name you got some impression what it would be.

So, a Conditional Split Transformation is a way by which you can conditionally split an input into multiple output.

Suppose, You are a food supplier and you cook both veg & non veg food. Now according to orders by client you need to move those order requests to particular kitchen to prepare those orders.

Let’s understand it by following step by step execution example.

In this example , I am using AdventureWorks database as a source database connection. We are fetching products along with categories. We need Bikes products in different file and rest other product in different file. You can see products with categories shown below in this image Accessories , bikes are the categories.

Query1

Step 1:- Create a  new package and drag drop data flow task control and double click it. You will get a new screen which data flow task. Now drag drop Source Assistance. from SSIS controls tool bar.

Step1

Now as usual we have to configure the database connection string and set the database to adventureworks.

step2

Step 2:- Once the database is configured right click on control and choose edit property. You will get below screen where you need to specify the query which we shown earlier in figure.

step3

Once you done with above steps you need to configure columns as well so, click on Columns and configure it.

step4

Step 3:- Now drag drop conditional split and connect input arrow to it as shown in figure

step5

Step 4:- Now choose edit option by right clicking the conditional split and configure it.  When you click you will get screen like below. You will find different condition operator. One important point to remember here you need to drag drop column name to condition textbox.  As our aim is to have product with Bikes category in a different file and other categories in different file.So we use following condition as shown in below figure.

step6

Step 5:-Now drag drop a flat file destination and assign the output of the above conditional split to File destination. You will get following screen. As you see we are assigning Bike condition output to this file which means all the products which belongs to bikes categories should be available in this.

Step7

Step 6: Now Configure the flat file destination by choosing the edit option.

step10

 

Step9

Step 7:- In similar way we have to capture the output which not belongs to Bikes category as shown in below figure

step8

Step 8: In nutshell we will get following screen

step11

We almost done here. Now to check whether this conditional split is working or not. To check this hit F5 or run the package. So, if you see below everything working fine. We got 32 rows in bike category and 73 rows in other categories.

Step12

To cross check result we will see the file output as well

BikeCategories

So , If you  go through all the above steps we have achieved Conditional split example.

I hope this post might help you to understand conditional split.

In next step we will go for next step in SSIS.

Enjoy !!!

RJ!!!

Step by Step SSIS learning What Data Conversion use in SSIS ? TIP#116

Dear Friends,

In the series of step by step learning of SSIS this is part #6 in which we are going to learn a new control Data Conversion.  I am sure by the name it clear that this control will be use when we require data type conversion of input columns then we need this control. This is exactly same like CONVERT  or CAST  in our programming.

Although, Here it would be important to share SSIS also have data type which are similar to other programming or database’s data types

Like DT_NUMERIC is equivalent to numeric data type, DT_I2 is equivalent to smallint, DT_str is equivalent to VARCHAR and so on.

I am sure you will find this control fairly simple so without wasting much time lets start step by step.

For a change I am using excel data source here.

Suppose, we have an excel of sales order which have columns like sales order, subtotal,Tax amount, and freight.

SaleOrderExcel

Here sales order is varchar, while SubTotal , Tax amount & Freight are numeric with decimal 4 places. We want to convert the numeric field in integer and want result in a flat file. so let’s start.

Step1 :- Drag drop a Data flow Task control and double click it. Now add an excel data source from SSIS tool bar as shown in below figure.

ExcelSource

Step 2:- Now next step is to configure this excel source. so to do this just right click and use EDIT option. You will get a screen where you need to provide the file which we want to access.

ExcelSourceStep2

Once the connection with excel is established the Next very important step is to configure sheet as highlighted in below figure. remember you need to configure this sheet because an excel may have number of sheets available so you need to assign proper sheet whose data you want to process.

ExcelSource3

Step 3: Once the sheet is configured you can choose the columns as well which you want to process or want in destination by click column’s option in as shown in below figure

ExcelSourceColumn

Step 4:- Now drag drop the data conversion control from SSIS toolbar

dataConvrsionControl

Step 5:- Now , open context menu and click edit button to configure data conversion. Now remember here we have to change data from numeric to integer (As discussed earlier our aim). so just change the desire data type which is integer (DT_I4) of all the three numeric data type. As shown in below figure. Also , point to remember here if when you do data type conversion you need to careful about Aliasing also. You will see copy of as a prefix on column.

Convesion

Step 6:- Now, we need this output in flat file so adding a file destination control from SSIS toolbox and configuring it by clicking Edit button as shown in below figure.

FlatFile

Step 7:- In next step we have to configure the file location and columns which we require in the flat file. And delete unnecessary columns which are not required.

deleteAccesscolumns

dataConvesionmapping

Step 8 : Once the above step is done, we are good to go to run this package. So,hold your breath for few second and hit F5 Smile .

You will get find below screen. Which shows that all the provided rows are inserted in file. 

result

Step 9 : To cross check whether file has integer columns or not lets open it.

CSVREsult

So, if you see above screen we did it successfully. So we achieved our goal in this post and used DATA conversion control successfully.

I hope you feel this post useful.

Thanks for reading this post.

Enjoy !!!

RJ