tag:blogger.com,1999:blog-65383210000572984972024-03-13T11:13:57.815-07:00SQL Server tips ,SQL Server tutorials, database tips- IndiandotnetSQL Raaga - a harmony Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.comBlogger139125tag:blogger.com,1999:blog-6538321000057298497.post-6743363158518974492017-03-28T11:58:00.000-07:002017-03-28T11:58:08.522-07:00How is easy to work with JSON in SQL SERVER 2016 ?<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="justify">
If you are a developer then surely you might have used
<strong>JSON (JavaScript Object Notation)</strong> but, if not then don’t worry
you might use sooner than later. <strong>JSON is kind of ecosystem</strong>
which is most popular in the various area for exchanging the data. If you talk
about<strong> charting solution, AJAX, Mobile services or any 3rd party
integration then generally JSON is the first choice of the developers.
</strong></div>
<div align="justify">
<br /></div>
<div align="justify">
If you see nowadays most of the NOSQL database like Microsoft
Azure Document DB, MONGODB etc. also using JSON ecosystem and some of them are
based on JSON.</div>
<div align="justify">
<br /></div>
<div align="justify">
As it is such a popular growing system So, why not in SQL
SERVER? </div>
<div align="justify">
In SQL SERVER 2016 JSON introduced. This we can say a step or
bridge between NON-relation database and relational database by Microsoft SQL
SERVER</div>
<div align="justify">
<br /></div>
<div align="justify">
SQL Server 2016 providing following capabilities when you are
using JSON </div>
<ol>
<li>
<div align="justify">
<strong>Parse JSON by relation query</strong></div>
</li>
<li>
<div align="justify">
<strong>Insert & update JSON using query</strong></div>
</li>
<li>
<div align="justify">
<strong>Store JSON in database</strong> </div>
</li>
</ol>
<div align="justify">
<br /></div>
<div align="justify">
If you see it then conceptually it is similar to<strong> XML
data type</strong> which you might use in SQL SERVER.</div>
<div align="justify">
The good thing in SQL SERVER 2016 for<strong> JSON there is no
Native data type.</strong> This will help in migration from any <strong>NOSQL
to SQL SERVER.</strong></div>
<div align="justify">
<br /></div>
<div align="justify">
SQL server provides <strong>bidirectional JSON
formatting</strong> which you can utilize in a various way. Suppose data is
coming from the external source in the JSON format then you can parse it and
store in table structure (if required) in another case external source require
data in JSON format while data in SQL SERVER in tabular format so both the
purpose can easily solve with <strong>SQL SERVER’s JSON feature.</strong></div>
<div align="justify">
<br /></div>
<div align="justify">
Now, let’s jump directly to the practical to check JSON
capabilities in SQL SERVER </div>
<div align="justify">
<br /></div>
<div align="justify">
<strong>1) FOR JSON AUTO</strong></div>
<div align="justify">
It is similar to <strong>FOR XML AUTO</strong>. It will
return JSON object of selected column where column name is treated as a Key or
in other words we can say it will format the query result in JSON.</div>
<div align="justify">
<a href="https://1.bp.blogspot.com/-fDKPojlfUx4/WNqt3tL8tLI/AAAAAAAAD-s/xYXhyAs3Ano5lQUxf1lEB5TPrtb3v3VTwCLcB/s1600/JSON_Feature_Indiandotnet_1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="166" src="https://1.bp.blogspot.com/-fDKPojlfUx4/WNqt3tL8tLI/AAAAAAAAD-s/xYXhyAs3Ano5lQUxf1lEB5TPrtb3v3VTwCLcB/s400/JSON_Feature_Indiandotnet_1.png" width="400" /></a> </div>
<div align="justify">
<br /></div>
<div align="justify">
</div>
<div align="justify">
when you run above command the result will be like as shown in
below figure.</div>
<div align="justify">
<a href="https://1.bp.blogspot.com/-pbalcr5OqTk/WNqt3j_gagI/AAAAAAAAD-o/j-cCzLKEKG4AEJZ-UfcIS1uK9hAa1h7CQCLcB/s1600/JSON_Feature_Indiandotnet_2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="66" src="https://1.bp.blogspot.com/-pbalcr5OqTk/WNqt3j_gagI/AAAAAAAAD-o/j-cCzLKEKG4AEJZ-UfcIS1uK9hAa1h7CQCLcB/s400/JSON_Feature_Indiandotnet_2.png" width="400" /></a> </div>
<div align="justify">
<br /></div>
<div align="justify">
<br /></div>
<div align="justify">
<strong>2) FOR JSON PATH: -</strong></div>
<div align="justify">
It’s exactly like JSON auto the only difference is instead of
SQL SERVER we have full control over the format. JSON Auto take predefined
column schema while with JSON path we can create a complex object.</div>
<div align="justify">
For example, we are using <strong>AdventureWorks</strong> Sales
order table and joining that with product table to get sub-node. If you see in
below image we have added Root node as well. This root Node can be added in JSON
auto as well if required.</div>
<div align="justify">
<a href="https://1.bp.blogspot.com/-h1tPkfJdb3k/WNqt3WHrnWI/AAAAAAAAD-k/K-9_nQltk7QpH_Xuy_2PXlduhvKTwM0nQCLcB/s1600/JSON_Feature_Indiandotnet_3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="151" src="https://1.bp.blogspot.com/-h1tPkfJdb3k/WNqt3WHrnWI/AAAAAAAAD-k/K-9_nQltk7QpH_Xuy_2PXlduhvKTwM0nQCLcB/s400/JSON_Feature_Indiandotnet_3.png" width="400" /></a> </div>
<div align="justify">
<br /></div>
<div align="justify">
<br /></div>
<div align="justify">
Now, when you run the above query we can get complex JSON
object as follows</div>
<div align="justify">
<a href="https://3.bp.blogspot.com/-89dNNUTOVCw/WNqt4TAdlzI/AAAAAAAAD-w/Gp9HChOvM4wwUCpGhJqAEi0OhL9fYzodQCLcB/s1600/JSON_Feature_Indiandotnet_4.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="207" src="https://3.bp.blogspot.com/-89dNNUTOVCw/WNqt4TAdlzI/AAAAAAAAD-w/Gp9HChOvM4wwUCpGhJqAEi0OhL9fYzodQCLcB/s400/JSON_Feature_Indiandotnet_4.png" width="400" /></a> </div>
<div align="justify">
<br /></div>
<div align="justify">
<strong>3) IsJSON function:-</strong></div>
<div align="justify">
By the name, it is clear that this is a validating function.
</div>
<div align="justify">
To cross check whether the provided string is a valid JSON or
not we can run <strong>ISJSON</strong>. </div>
<div align="justify">
<a href="https://1.bp.blogspot.com/-7t4Ip27a1bs/WNqt4TPWMAI/AAAAAAAAD-0/zgXIo1-yFnQAzlErrteOex-527czJGeywCLcB/s1600/JSON_Feature_Indiandotnet_5.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="https://1.bp.blogspot.com/-7t4Ip27a1bs/WNqt4TPWMAI/AAAAAAAAD-0/zgXIo1-yFnQAzlErrteOex-527czJGeywCLcB/s400/JSON_Feature_Indiandotnet_5.png" width="400" /></a></div>
<div align="justify">
<br /></div>
<div align="justify">
<strong>4) JSON_VALUE:-</strong></div>
<div align="justify">
By the name, it is clear that if you want to get the value of
the particular key of JSON then you can use this beautiful function which is
<strong>JSON_VALUE.</strong></div>
<div align="justify">
<br /><a href="https://1.bp.blogspot.com/-XWWh1CfG758/WNqt4jtPXzI/AAAAAAAAD-4/XIBy1FZ0PUw8KjKcJNQGAYmlqnFn2IPkACLcB/s1600/JSON_Feature_Indiandotnet_6.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="107" src="https://1.bp.blogspot.com/-XWWh1CfG758/WNqt4jtPXzI/AAAAAAAAD-4/XIBy1FZ0PUw8KjKcJNQGAYmlqnFn2IPkACLcB/s400/JSON_Feature_Indiandotnet_6.png" width="400" /></a></div>
<div align="justify">
<br /></div>
<div align="justify">
<strong>5) OPENJSON function:- </strong></div>
<div align="justify">
This is a very beautiful function which you can use to parse
external schema. Suppose, you got a JSON string from a mobile service which you
will directly pass to SQL Sever and SQL SERVER stored procedure will do rest of
the operation to parse it. The parsing and other operation can be easily handled
by <strong>OPENJSON</strong>. The only tweak here that it required database
compatibility level 130 which you need to do (if not <strong>compatible with
level 130</strong>)</div>
<div align="justify">
<a href="https://1.bp.blogspot.com/--CPoZKWcrq0/WNqt5DrCiCI/AAAAAAAAD-8/lY2lmOeV2cMilN-zixLvc8FLpbtmjBdxgCLcB/s1600/JSON_Feature_Indiandotnet_7.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="95" src="https://1.bp.blogspot.com/--CPoZKWcrq0/WNqt5DrCiCI/AAAAAAAAD-8/lY2lmOeV2cMilN-zixLvc8FLpbtmjBdxgCLcB/s400/JSON_Feature_Indiandotnet_7.png" width="400" /></a> </div>
<div align="justify">
<br /></div>
<div align="justify">
<br /></div>
<div align="justify">
There are many other interesting things which we will cover
later. </div>
<div align="justify">
Please, provide your inputs.</div>
<div align="justify">
RJ</div>
</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-20694606909596087002017-03-27T01:43:00.000-07:002017-03-27T01:43:37.715-07:00How easy it is to implement Row Level Security in SQL SERVER 2016 ?<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
To understand <b>RLS (ROW LEVEL SECURITY)</b> let’s understand the
different problems first.</div>
<div style="text-align: justify;">
<b>Problem 1</b> Suppose, you have a Multi-tenant e-commerce
website and different companies registered on your website and you have
centralized single database for all the client. Now as a product owner it is
your responsibility that one tenant’s data should not be available to another
tenant. This is a very common problem. </div>
<div style="text-align: justify;">
2. Now, Suppose you have hospital database in which you have login user of
different doctors & nurses. Now, your challenge is to show data to doctor or
nurses to their relevant patient to whom they are giving treatment, not any
other patient data should be available .</div>
<div style="text-align: justify;">
Here, limiting the user’s access to only certain rows of the data in database
many have various reasons like <b>compliance standards, regulatory
need</b> or security reasons.</div>
<div style="text-align: justify;">
Now, I know you were thinking that all the above problem can be resolved at
code side easily by writing custom logic. I will say here yes you are right but
this is not the 100% solution. For example, if you have 4 different application
like web, mobile, console, windows (Excel) and all has their own DAL then you
have to implement this custom logic to every application and suppose tomorrow
if any time a new 3rd party came which want to integrate your data or access
database directly then in such cases it is tuff to apply same logic. </div>
<div style="text-align: justify;">
So, all the above problem can be easily handle using SQL SERVER 2016’s
feature which is ROW Level Security (RLS). Security is one of the key areas
which is handled in SQL SERVER 2016 very seriously. As <b>RLS (Row Level
Security) i</b>s centralized security logic so you don’t need to repeat
same security logic again and again.</div>
<div style="text-align: justify;">
As the name suggested Security implemented at Row Level in <b>SQL SERVER
2016</b>. In the <b>Row Level, Security</b> data is access
according to user roles. It is a centralized data access Logic.</div>
<div style="text-align: justify;">
RLS has following properties</div>
<ul style="text-align: justify;">
<li>Fine-grained access role ( control both read & write access to specific
rows)
</li>
<li>Application transparency ( No application changes required)
</li>
<li>Centralized the access within the database
</li>
<li>Easy to implement & maintain </li>
</ul>
<div style="text-align: justify;">
<b>How RLS works?</b></div>
<div style="text-align: justify;">
RLS is a predicate based function which runs seamlessly every time when a
SQL is run on particular table on which RLS predicate function implemented.</div>
<div style="text-align: justify;">
There are 2 predicates which can be implemented in RLS</div>
<div style="text-align: justify;">
1) Filter Predicate: - By the name, it is clear that it will filter the row
or we can say exclude the rows which do not satisfy the predicate and stop
further option like select, Update & Delete. </div>
<div style="text-align: justify;">
for example: Suppose, you want to restrict doctor to see other doctor’s
patient data then in such case you can apply filter predicate.</div>
<div style="text-align: justify;">
2) Block Predicate: - This predicate helps in implementing policy by which
insert, update and delete rows will prevent which violate the filter predicate.
In other words, we can say it explicitly block write operation. </div>
<div style="text-align: justify;">
For example, you have multi-tenant application and you want to restrict one
tenant user to insert or update other tenant’s data. Or suppose you have sales
representative who belongs to specific region so they can not insert , update or
delete other region’s data.</div>
<div style="text-align: justify;">
<b>Demo:-</b></div>
<div style="text-align: justify;">
I know you will be super excited to see the demo of this feature so. Let’s do
it right away.</div>
<div style="text-align: justify;">
There are 2 basic steps to create RLS </div>
<div style="text-align: justify;">
a) Create inline table function or we can say predicate function and write
custom logic to control user access to every row</div>
<div style="text-align: justify;">
b) create the security policy and apply it.</div>
<div style="text-align: justify;">
In this demo ,I am creating a new table called Patients which has following
schema. </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-d_GZTaSUEY8/WNjO86sMtaI/AAAAAAAAD9k/EYt8R2Vw_kUW8YGwe7CZnc6v0Q8ZHlu9QCLcB/s1600/RLS_Demo_Indiandotnet_1.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="117" src="https://1.bp.blogspot.com/-d_GZTaSUEY8/WNjO86sMtaI/AAAAAAAAD9k/EYt8R2Vw_kUW8YGwe7CZnc6v0Q8ZHlu9QCLcB/s400/RLS_Demo_Indiandotnet_1.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Here, I have inserted 2 rows for Nurse1 & 2 rows for Nurse2</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-WZQgKkQzX44/WNjO9fxtK4I/AAAAAAAAD9w/aYlIKidGOqwN51uIX0Y05F6KZjVAsMtrgCEw/s1600/RLS_Demo_Indiandotnet_2.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="147" src="https://1.bp.blogspot.com/-WZQgKkQzX44/WNjO9fxtK4I/AAAAAAAAD9w/aYlIKidGOqwN51uIX0Y05F6KZjVAsMtrgCEw/s400/RLS_Demo_Indiandotnet_2.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The objective is to show only those rows to Nurse1, Nurse2 in which they are
the in charge and a doctor user can see entire table’s data.</div>
<div style="text-align: justify;">
To achieve this let first create 3 users in database </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-QAax5WbT-5Y/WNjO9UG2FVI/AAAAAAAAD90/93VO3j-rs6YXag65lKEhGoux7gTpaghuwCEw/s1600/RLS_Demo_Indiandotnet_3.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="100" src="https://1.bp.blogspot.com/-QAax5WbT-5Y/WNjO9UG2FVI/AAAAAAAAD90/93VO3j-rs6YXag65lKEhGoux7gTpaghuwCEw/s320/RLS_Demo_Indiandotnet_3.PNG" width="320" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Once the users are created the next step is to grant permission of select to
Nurse1 & Nurse2 user and full permission to doctor user.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-mDjIG_qxhAA/WNjO9fsnMWI/AAAAAAAAD94/4kXVrW2rV-YZDV4FO_r5YXkKHc6jC5TfgCEw/s1600/RLS_Demo_Indiandotnet_4.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="116" src="https://1.bp.blogspot.com/-mDjIG_qxhAA/WNjO9fsnMWI/AAAAAAAAD94/4kXVrW2rV-YZDV4FO_r5YXkKHc6jC5TfgCEw/s400/RLS_Demo_Indiandotnet_4.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now, before creating function it is a standard to create a security schema in
our case we are creating a schema with name sec as shown in below figure.</div>
<div style="text-align: justify;">
Now, create a function which will have security logic. The Logic is very
simple if the user is doctor Or any in charge name then return 1 else 0.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-8Te9UuJceYU/WNjO9wuShTI/AAAAAAAAD98/LLI0l9KG5UUlB0-pQqkAX0mFoG26Y09DACEw/s1600/RLS_Demo_Indiandotnet_5.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="141" src="https://3.bp.blogspot.com/-8Te9UuJceYU/WNjO9wuShTI/AAAAAAAAD98/LLI0l9KG5UUlB0-pQqkAX0mFoG26Y09DACEw/s400/RLS_Demo_Indiandotnet_5.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now create a security policy to proceed further</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-ioLf1x089VY/WNjO94r6Y2I/AAAAAAAAD-E/QpRVcsIJaKsKJBQYXAaDw57B6TBILARpwCEw/s1600/RLS_Demo_Indiandotnet_6.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="92" src="https://2.bp.blogspot.com/-ioLf1x089VY/WNjO94r6Y2I/AAAAAAAAD-E/QpRVcsIJaKsKJBQYXAaDw57B6TBILARpwCEw/s400/RLS_Demo_Indiandotnet_6.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Till now we are good to go. Now, let’s test the security policy.</div>
<div style="text-align: justify;">
Firstly, running the select query with default user “dbo.” and we have not
given permission for this user if you see fn_RLSPredicate we have not mentioned
it so obviously the result would show “0” records.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-Ej7vcuXqU-g/WNjO99uYd8I/AAAAAAAAD-A/BkrlnR1lnlQNLDoybSiACZlYFw-4a6ulQCEw/s1600/RLS_Demo_Indiandotnet_7.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="130" src="https://3.bp.blogspot.com/-Ej7vcuXqU-g/WNjO99uYd8I/AAAAAAAAD-A/BkrlnR1lnlQNLDoybSiACZlYFw-4a6ulQCEw/s400/RLS_Demo_Indiandotnet_7.PNG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now, running the same select statement but executing with “Nurse1” login then
you will find 2 records which are relevant to Nurse1 is visible.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-E4cjVIJWzYA/WNjO-QvSNTI/AAAAAAAAD-Q/78ni0f-yWsIPOpknC849G5WEu_zBeKA7wCEw/s1600/RLS_Demo_Indiandotnet_8.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="151" src="https://2.bp.blogspot.com/-E4cjVIJWzYA/WNjO-QvSNTI/AAAAAAAAD-Q/78ni0f-yWsIPOpknC849G5WEu_zBeKA7wCEw/s400/RLS_Demo_Indiandotnet_8.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Similarly, I am running the same statement for Nurse2 user by running command
“Execute as user” so, again I will get 2 records</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-ttMaKhKFInE/WNjO-clTwCI/AAAAAAAAD-Q/9TGgH5-77rI65KM2gSZFzUa0NNk9jYXlwCEw/s1600/RLS_Demo_Indiandotnet_9.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="148" src="https://1.bp.blogspot.com/-ttMaKhKFInE/WNjO-clTwCI/AAAAAAAAD-Q/9TGgH5-77rI65KM2gSZFzUa0NNk9jYXlwCEw/s400/RLS_Demo_Indiandotnet_9.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Now, running the same statement with Doctor user and as per our expectation,
it should show all 4 records.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-GidnllW82K0/WNjO839-_4I/AAAAAAAAD-Q/EbKslMo7u_YisM5tEv1O0kOlJ5oUtFrDQCEw/s1600/RLS_Demo_Indiandotnet_10.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" src="https://4.bp.blogspot.com/-GidnllW82K0/WNjO839-_4I/AAAAAAAAD-Q/EbKslMo7u_YisM5tEv1O0kOlJ5oUtFrDQCEw/s400/RLS_Demo_Indiandotnet_10.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
So, as you can see we have achieved the goal using RLS (Row Level Security)
feature. Now, next thing which might occur in your mind how to disable this
policy if required then doesn’t worry it is very simple. Just alter the security
policy and make state = off as shown in below figure.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-CQqJoSb3zdU/WNjO86jkNsI/AAAAAAAAD-Q/Tj7HS1c1jJIR4B-b8bFBBOuwGEDMfiOzwCEw/s1600/RLS_Demo_Indiandotnet_11.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="70" src="https://4.bp.blogspot.com/-CQqJoSb3zdU/WNjO86jkNsI/AAAAAAAAD-Q/Tj7HS1c1jJIR4B-b8bFBBOuwGEDMfiOzwCEw/s400/RLS_Demo_Indiandotnet_11.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
I hope till now we are good to work on RLS. In next couple of post, we will
dig deeper in RLS.</div>
<div style="text-align: justify;">
Please, share your thought for RLS.</div>
</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-46952960421774907842016-11-27T05:50:00.001-08:002016-11-27T05:50:47.554-08:00Why Do People Think Dynamic Data Masking is a Good Idea? - SQL SERVER 2016 #5<div dir="ltr" style="text-align: left;" trbidi="on">
Data security is always one of the important points which can not be ignored.
Nowadays if you are working for any specific domain like Banking or Healthcare
then there are a lot of compliance rules which you have to follow.<br />
Data Masking is one of the best ways to help you to secure your sensitive
data by a dynamic mask encryption. <br />
This is one of the best features of SQL SERVER 2016 which I personally like
most. <br />
With the help of Dynamic Data Masking, you are just applying a mask to your
sensitive data. for example, if your system is storing SSN data then it should
be visible to privileged or we can say authorized user only.<br />
Dynamic Data Masking has following features:-<br />
1) It masked the Sensitive data.<br />
2) There will be no impact on functions & Stored Procedures and other SQL
statement after applying this.<br />
3) Applying the Data Masking is super easy. <br />
4) You can allow any database user/role to see unmasked data by just simple
Grant & Revoke Statement .<br />
5) Data is not physically changed.<br />
6) It is just on the fly obfuscation of data query result .<br />
7) It is just a T-SQL command with basic syntax.<br />
Now , let us understand how to implement it.<br />
Data masking implementation is very easy and below is the syntax for it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-AAIN6AZCLgY/WDri6vOTCsI/AAAAAAAAD6s/k4s_v08rkOECGrlDlPRqSuBgyPZzCWylACLcB/s1600/Masksyntax1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="50" src="https://4.bp.blogspot.com/-AAIN6AZCLgY/WDri6vOTCsI/AAAAAAAAD6s/k4s_v08rkOECGrlDlPRqSuBgyPZzCWylACLcB/s400/Masksyntax1.png" width="400" /></a></div>
<br />
<br />
Here, if you see the syntax is very simple the only new thing is
<strong>MASKED and with (function=function name) </strong>only.<br />
The function is nothing but the way to mask the data. SQL SERVER 2016 has
following different functions to mask the data<br />
1) <strong>Default() function</strong>:- This is basic masking with the help
of this function you can easily mask any field.<br />
for example, your first name or last name field can be masked like XXXX
etc.<br />
2) <strong>Email() function</strong> :- If your column is email type or you
we can say if you store Email in your column then you should use the Email()
function for masking.<br />
for example, your email can be mask like R<a href="mailto:RXXXX@XXXX.com">XXXX@XXXX.com</a><br />
3) <strong>Partial () function</strong>:- With the help of this function you
can mask specific data length and exclude some part of data from masking logic.
for example, 123-4567-789 is your phone number then with partial masking feature
you can mask like 12X-XXXX-7XX.<br />
4)<strong> Random() function</strong> – By the name it is clear that you can
mask the data with any random number range we will see more below in the hands
on.<br />
<strong>Remove Masking</strong> :- This is also possible that you applied a
masking to a column and later on you don’t want that masking. So , don’t worry
it very easy to remove masking from a column. below is the syntax for same.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-QxTrYY2QGKg/WDri4L6uDKI/AAAAAAAAD6A/tXCzc2wVAU0LjNJrjXrmonkN6UPVnOHbQCEw/s1600/DropMask.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="100" src="https://1.bp.blogspot.com/-QxTrYY2QGKg/WDri4L6uDKI/AAAAAAAAD6A/tXCzc2wVAU0LjNJrjXrmonkN6UPVnOHbQCEw/s400/DropMask.png" width="400" /></a></div>
<br />
<br />
Now, let’s understand this by an example. <br />
In the example we are using a new database “SecureDataMask” in this database
we are creating a tblSecureEmployee as shown in below figure.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-cjzIhFth8GQ/WDri4ABijSI/AAAAAAAAD58/r1BVqO6Ohr0l0sy86OhKH6wc-_lBo_UjACEw/s1600/Create_Table_Secure_Employee.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="265" src="https://3.bp.blogspot.com/-cjzIhFth8GQ/WDri4ABijSI/AAAAAAAAD58/r1BVqO6Ohr0l0sy86OhKH6wc-_lBo_UjACEw/s640/Create_Table_Secure_Employee.png" width="640" /></a></div>
<br />
<br />
Now, in this table, we are inserting couple of data for testing as shown
below<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-lyVgFUYpZdk/WDri5NoVd1I/AAAAAAAAD6M/M3WPYg4kOtUvhAJfjBEU7nTX3LHF24s0ACEw/s1600/Indiandotnet_Insert_Default_Row.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="160" src="https://1.bp.blogspot.com/-lyVgFUYpZdk/WDri5NoVd1I/AAAAAAAAD6M/M3WPYg4kOtUvhAJfjBEU7nTX3LHF24s0ACEw/s640/Indiandotnet_Insert_Default_Row.png" width="640" /></a></div>
<br />
<br />
Now we are applying different masking on this table’s column<br />
<strong>1) Default Masking :</strong> In the table, we are applying default
masking on LastName<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-PMY8wppcfQU/WDri45gUJqI/AAAAAAAAD6I/S83XpF9Pvs8BtGlBUeKB3ldL6PX8qEwEQCEw/s1600/Indiandotnet_Default_Masking.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="78" src="https://2.bp.blogspot.com/-PMY8wppcfQU/WDri45gUJqI/AAAAAAAAD6I/S83XpF9Pvs8BtGlBUeKB3ldL6PX8qEwEQCEw/s640/Indiandotnet_Default_Masking.png" width="640" /></a></div>
<br />
<br />
2) <strong>Email Masking :-</strong> In the table, we are going to apply
Email masking to email column below is the syntax for it.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-deGxFRjM6z8/WDri4JputoI/AAAAAAAAD6w/SthR6-fCD6wQC8QCG0-K9icqZrNtOFAEQCEw/s1600/Indiandotnet_Apply_Email_Masking.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="60" src="https://2.bp.blogspot.com/-deGxFRjM6z8/WDri4JputoI/AAAAAAAAD6w/SthR6-fCD6wQC8QCG0-K9icqZrNtOFAEQCEw/s640/Indiandotnet_Apply_Email_Masking.png" width="640" /></a></div>
<br />
<br />
<strong>3) Partial Masking:- </strong>For SSN we are going to apply custom
masking. below is the syntax for same. Here as we aware that SSN is 11
characters long in our database. we applied the partial masking to show first
two & last two characters in original value and rest other in the mask.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-La7hOnAW8Y4/WDri6A4QgsI/AAAAAAAAD6w/4JyuDzMtdYsieSNNgpnUGyK5TA3_lOVjQCEw/s1600/Indiandotnet_Partial_Masking_SQL_SERVER_2016.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="56" src="https://2.bp.blogspot.com/-La7hOnAW8Y4/WDri6A4QgsI/AAAAAAAAD6w/4JyuDzMtdYsieSNNgpnUGyK5TA3_lOVjQCEw/s640/Indiandotnet_Partial_Masking_SQL_SERVER_2016.png" width="640" /></a></div>
<br />
<br />
<strong>4) Random Number Masking :-</strong> In our table, we are going to
apply Random number masking to Securepin column as shown below.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-XU_B6b06cJs/WDri57edbII/AAAAAAAAD6w/Eq9nw7AcWSsyAUqWRUaPtucwsSRJdxA6ACEw/s1600/Indiandotnet_Random_Number_Masking_SQL_SERVER_2016.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="51" src="https://1.bp.blogspot.com/-XU_B6b06cJs/WDri57edbII/AAAAAAAAD6w/Eq9nw7AcWSsyAUqWRUaPtucwsSRJdxA6ACEw/s640/Indiandotnet_Random_Number_Masking_SQL_SERVER_2016.png" width="640" /></a></div>
<br />
<br />
Here, so far we are done with all the masking now. let me run the select
statement to test it.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-gW3DJCFsjwc/WDri5YIAuwI/AAAAAAAAD6w/KfKjj4PAHOoezX-MNhILOhAcSB9I63oOgCEw/s1600/Indiandotnet_Last_Name_Visible_.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="209" src="https://2.bp.blogspot.com/-gW3DJCFsjwc/WDri5YIAuwI/AAAAAAAAD6w/KfKjj4PAHOoezX-MNhILOhAcSB9I63oOgCEw/s640/Indiandotnet_Last_Name_Visible_.png" width="640" /></a></div>
<br />
<br />
If you see the data is still in the original state because I logged in using
privilege account “SA”. now, to test the masking let me create a new user
account.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-mZONPsDrciI/WDri4i-n-PI/AAAAAAAAD6w/FP6AiU6EIhQV9LtRCgBDSqyElvKVRxkHACEw/s1600/Indiandotnet_Create_Login_User_SQL_SERVER_.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="88" src="https://3.bp.blogspot.com/-mZONPsDrciI/WDri4i-n-PI/AAAAAAAAD6w/FP6AiU6EIhQV9LtRCgBDSqyElvKVRxkHACEw/s640/Indiandotnet_Create_Login_User_SQL_SERVER_.png" width="640" /></a></div>
<br />
<br />
After creating the account we are trying to log-in with a new account as
shown in below screen.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-tZcT9cA-bZk/WDri5eHiwDI/AAAAAAAAD6w/YpCPNO2qeHw_C00qhVx2W8GQkSOZ51GlACEw/s1600/Indiandotnet_Login_With_New_User.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="352" src="https://4.bp.blogspot.com/-tZcT9cA-bZk/WDri5eHiwDI/AAAAAAAAD6w/YpCPNO2qeHw_C00qhVx2W8GQkSOZ51GlACEw/s640/Indiandotnet_Login_With_New_User.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<br />
After our successful log in, we will run the select statement on same
database’s table as we did earlier. If you see below snap you will find that we
got masked data for LastName, Email, SSN, and securePin.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-ssXYkB2hoOs/WDri53j3BrI/AAAAAAAAD6w/VBbCyBszO3kyqDnypvvGPCvGu6S8S548ACEw/s1600/Indiandotnet_Masked_Data_With_Less_Preivileged_account.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://1.bp.blogspot.com/-ssXYkB2hoOs/WDri53j3BrI/AAAAAAAAD6w/VBbCyBszO3kyqDnypvvGPCvGu6S8S548ACEw/s640/Indiandotnet_Masked_Data_With_Less_Preivileged_account.png" width="640" /></a></div>
<br />
<br />
<br />
Now, it might be a rare case but suppose you want to remove the mask from any
column on which you applied masking then don’t worry it is super easy. <br />
Suppose, from the same table we don’t want mask on the LastName then below is
the syntax for same.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-8W6qtu4slaM/WDri6byP1gI/AAAAAAAAD6w/aeNk3Krch4czZulVsy_aM58VpFN_fyyPgCEw/s1600/Indiandotnet_Removing_Mask_from_column_Sqlserver2016.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://4.bp.blogspot.com/-8W6qtu4slaM/WDri6byP1gI/AAAAAAAAD6w/aeNk3Krch4czZulVsy_aM58VpFN_fyyPgCEw/s1600/Indiandotnet_Removing_Mask_from_column_Sqlserver2016.png" /></a></div>
<br />
Now, let me run the same select statement <strong>seeMask_user. </strong>You
will find the Last Name is unmasked now.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://2.bp.blogspot.com/-gW3DJCFsjwc/WDri5YIAuwI/AAAAAAAAD6w/KfKjj4PAHOoezX-MNhILOhAcSB9I63oOgCEw/s1600/Indiandotnet_Last_Name_Visible_.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="210" src="https://2.bp.blogspot.com/-gW3DJCFsjwc/WDri5YIAuwI/AAAAAAAAD6w/KfKjj4PAHOoezX-MNhILOhAcSB9I63oOgCEw/s640/Indiandotnet_Last_Name_Visible_.png" width="640" /></a></div>
<br />
<br />
From above few changes you can secure your data via Dynamic masking and as
mentioned above there will be no impact on your existing function ,stored
procedure because data is not physically changed.<br />
I hope you may like this feature. Please, share your input for same.<br />
Enjoy !! <br />
RJ </div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-16298730473263844072016-10-22T20:15:00.003-07:002016-10-22T20:15:49.051-07:00How DATEDIFF_BIG a new feature of SQL SERVER 2016 Can Keep You Out of Trouble<div dir="ltr" style="text-align: left;" trbidi="on">
In the series of SQL SERVER 2016, this is a new post. in this post, we will
discuss DATEDIFF_BIG and how it is helpful.<br />
So, before jumping into directly in technical details, we all know that time
is very important and every second valuable and countable but sometimes every
microsecond & nanosecond is also countable <img alt="Smile" class="wlEmoticon wlEmoticon-smile" src="file:///C:/Users/Rajat.Jaiswal/AppData/Local/Temp/WindowsLiveWriter1286139640/supfiles4BBFABB/wlEmoticon-smile%5B2%5D.png" style="border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none;" />
. For such operations in which every microsecond & nanosecond is countable,
we can use <strong>DATEDIFF_BIG function.</strong><br />
As you aware the <strong>BIGINT range </strong>is from<strong>
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.</strong> Here if any
difference (Micro & Nano) second is out of the the mentioned range then
DATEDIFF returns that value else return error<strong>(Obviously).</strong><br />
Below is the basic syntax if <strong>DATEDIFF_BIG</strong> although it is
similar to <strong>DATEDIFF.</strong> We can say it is a extended version of
<strong>DATEDIFF.</strong><br />
<strong>DATEDIFF_BIG( datePart, start Date, End date)</strong><br />
The value of <strong>datePart</strong> is same like<strong> DATEDIFF
function.</strong><br />
For example if you want to collect millisecond difference then use
<strong>ms</strong>, microsecond then <strong>mcs</strong> and for nanosecond
<strong>ns.</strong><br />
As per the <a href="https://msdn.microsoft.com/en-us/library/mt628058.aspx" target="_blank">MSDN</a> for the <strong>Millisecond,</strong> the maximum
difference between start date & end date is 24 days, 20 hours, 21 minutes
and 23,647 seconds. For <strong>Second, </strong>the maximum difference is
<strong>68 years. </strong><br />
Now, let see why this DATEDIFF_BIG introduced so, I am running a
<strong>DATEDIFF </strong>function in SQL SERVER 2012 and see what we get after
running that query.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-7y8A3TFcc8I/WAwrDs2sVOI/AAAAAAAAD3s/S-wAkIpA3qQw7x9Y0xQD8nHLRLZR_iaDQCLcB/s1600/DATEDIFF_Function_Issue_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="DATEDIFF_BIG in SQL SERVER 2016" border="0" height="73" src="https://4.bp.blogspot.com/-7y8A3TFcc8I/WAwrDs2sVOI/AAAAAAAAD3s/S-wAkIpA3qQw7x9Y0xQD8nHLRLZR_iaDQCLcB/s640/DATEDIFF_Function_Issue_Indiandotnet.png" title="" width="640" /></a></div>
<br />
<br />
<br />
<br />
You can see in above query we got an error of overflow.<br />
Now, we are calculating the same difference from<strong> DATEDIFF_BIG in SQL
SERVER 2016.</strong> See, below snap for same.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-fZKHrG-qras/WAwrDqrkPgI/AAAAAAAAD3o/rz7cYN4tnsorq1UfKkcn76UNX_0FcA9MACEw/s1600/DATEDIFF_BIG_FUNCTION_INDIANDOTNET.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="DATEDIFF_BIG in SQL SERVER 2016 by Indiandotnet" border="0" height="84" src="https://1.bp.blogspot.com/-fZKHrG-qras/WAwrDqrkPgI/AAAAAAAAD3o/rz7cYN4tnsorq1UfKkcn76UNX_0FcA9MACEw/s640/DATEDIFF_BIG_FUNCTION_INDIANDOTNET.png" title="" width="640" /></a></div>
<br />
<br />
<br />
<br />
Isn’t it great ? Although, I am scarred with those applications who calculate
milliseconds <img alt="Sad smile" class="wlEmoticon wlEmoticon-sadsmile" src="file:///C:/Users/Rajat.Jaiswal/AppData/Local/Temp/WindowsLiveWriter1286139640/supfiles4BBFABB/wlEmoticon-sadsmile%5B2%5D.png" style="border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none;" />.<br />
Anyways, it is good to know feature. <br />
Do provide your feedback for the post it is very valuable for us.<br />
RJ !!!</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-6036102405883282512016-10-22T02:22:00.001-07:002016-10-22T02:22:12.945-07:00If You Read One Article About Split String in SQL SERVER 2016s Read this One #3<div dir="ltr" style="text-align: left;" trbidi="on">
In the <strong>Series of SQL SERVER 2016,</strong> 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 <strong>SQL SERVER versions</strong> either you will write a function
which split the string and return desire values in a column or<br />
you will use XML function or might be different custom functions.<br />
Let me explain this with below example. Suppose you have a string like
below<br />
DECLARE @FriendList AS VARCHAR(1000)<br />
SET @FriendList ='Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed'<br />
<br />
Now you want output like below <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-WwlXnLez6zw/WAsuYdB7SkI/AAAAAAAAD3M/gMdSRV4hW4EuTVS6epVoTvv5I03UNYs5QCEw/s1600/output_of.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="String split in SQL SERVER 2016" border="0" height="320" src="https://3.bp.blogspot.com/-WwlXnLez6zw/WAsuYdB7SkI/AAAAAAAAD3M/gMdSRV4hW4EuTVS6epVoTvv5I03UNYs5QCEw/s320/output_of.png" title="" width="237" /></a></div>
<br />
<br />
Then in such cases, you will follow <strong>2 approaches</strong> (their
might be other as well)<br />
<br />
Approach 1:- Write a function like below and use it.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-H6kPeCmjy1U/WAsuYkAp_ZI/AAAAAAAAD3c/GcD936RBnug3lV4JN6pCKOvXxAz4beEEwCEw/s1600/Traditional_way_Split_String_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Different ways of spliting a comma seperated string in SQL " border="0" height="193" src="https://3.bp.blogspot.com/-H6kPeCmjy1U/WAsuYkAp_ZI/AAAAAAAAD3c/GcD936RBnug3lV4JN6pCKOvXxAz4beEEwCEw/s320/Traditional_way_Split_String_Indiandotnet.png" title="" width="320" /></a></div>
<br />
<br />
And once this function is created you can use like below<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://4.bp.blogspot.com/-JBEcEFdoQbA/WAsuZNftwjI/AAAAAAAAD3c/0zVmc_OviogotfpG79KnPNCsa-k8ZxT0gCEw/s1600/use_of_Split_Function_in_SQL_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="custom string split function " border="0" height="196" src="https://4.bp.blogspot.com/-JBEcEFdoQbA/WAsuZNftwjI/AAAAAAAAD3c/0zVmc_OviogotfpG79KnPNCsa-k8ZxT0gCEw/s320/use_of_Split_Function_in_SQL_Indiandotnet.png" title="" width="320" /></a></div>
<br />
<br />
Approach 2 :- You can use XML option in SQL SERVER as shown in below<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-zkNM-1p01sw/WAsuYKzSK1I/AAAAAAAAD3c/b1csU6X2gWMqeFv7WfIsM31Y0YkUXtvRACEw/s1600/String_split_by_XML_SQL_SERVER.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="split string using XML in SQL SERVER " border="0" height="176" src="https://1.bp.blogspot.com/-zkNM-1p01sw/WAsuYKzSK1I/AAAAAAAAD3c/b1csU6X2gWMqeFv7WfIsM31Y0YkUXtvRACEw/s640/String_split_by_XML_SQL_SERVER.png" title="" width="640" /></a></div>
<br />
So, the good news is now in<strong> SQL SERVER 2016 </strong>you don’t need
to write so many lines to split any string. In <strong>SQL SERVER 2016
</strong>a new string function is Introduced which is<br />
<a href="https://msdn.microsoft.com/en-in/library/mt684588.aspx" target="_blank">STRING_SPLIT</a><br />
The use of this function is very easy and below is the syntax <br />
<strong>STRING_SPLIT (string, separator)</strong><br />
Now, let me show you same output using STRING_SPLIT function<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-RKnVz8wltfc/WAsuZFgozGI/AAAAAAAAD3c/0YgzPg93K284kLUlyoJ66JkeR3OLuEBGwCEw/s1600/string_Split_Function_sql_server_2016_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="string_split function in SQL SERVER 2016" border="0" height="396" src="https://1.bp.blogspot.com/-RKnVz8wltfc/WAsuZFgozGI/AAAAAAAAD3c/0YgzPg93K284kLUlyoJ66JkeR3OLuEBGwCEw/s640/string_Split_Function_sql_server_2016_Indiandotnet.png" title="" width="640" /></a></div>
<br />
<br />
Isn’t it easy ?<br />
I hope you will like this easy way to split the string.<br />
Provide your feedback.<br />
RJ !!!</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-23839602868893148412016-10-16T08:46:00.002-07:002016-10-16T08:46:37.741-07:00Do you know Compress & Decompress function in SQL SERVER 2016 ?<div dir="ltr" style="text-align: left;" trbidi="on">
This is another article in the series of <strong>SQL SERVER 2016 Journey
</strong>. I am pretty much sure you might aware of <strong>Gzip Compression
algorithm</strong>. If not then try this <a href="https://www.google.co.in/search?q=gzip+compression">link</a>.<br />
<br />
So, <strong>SQL SERVER 2016</strong> introduce this two awesome functions for
C<strong>ompress & Decompress</strong> the data. <br />
Before SQL SERVER 2016 version we have <strong>data compression feature like
Page & Row compression (<a href="http://sqlraaga.blogspot.in/2015/03/data-compression-unique-feature-of-sql.html">check
Previous post for it Link</a> )</strong>which is different then this column
value compression.<br />
<br />
In SQL SERVER 2016 Compress function, <strong>data compression is done via
GZIP</strong> algorithm and return <strong>VARBINARY(MAX). </strong><br />
<br />
Below is the simple syntax of Compress function<br />
<br />
<strong>Compress (Expression)</strong><br />
<br />
Here Expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max),
varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) <br />
<br />
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)
.<br />
<br />
below is the simple syntax of <strong>Decompress <br />Decompress (Compressed
string) </strong><br />
<br />
Let’s understand this via an example as shown below .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-8U1i5dXVqIA/WAOgLtlzznI/AAAAAAAAD20/ZoCdqBd1yP0o0NyNuYSDosq_PP4vrQPPwCLcB/s1600/Indiandotnet_Compress_Decompress_Feature_SQL.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Compress function" border="0" height="242" src="https://1.bp.blogspot.com/-8U1i5dXVqIA/WAOgLtlzznI/AAAAAAAAD20/ZoCdqBd1yP0o0NyNuYSDosq_PP4vrQPPwCLcB/s400/Indiandotnet_Compress_Decompress_Feature_SQL.png" title="" width="400" /></a></div>
<br />
In this example I have taken 3 tables with exact same schema & data <br />
<br />
<ol>
<li>1) IndiandotnetFriends
</li>
<li>2) IndiandotnetFriends_Compress
</li>
<li>3) IndiandotneFriends_Decompress </li>
</ol>
<br />
You can see snap in which we are inserting same data.<br />
As the name suggested in first table normal data from Adventureworks’s person
table. <br />
In second table we are inserting compressed value of first Name and in 3rd
table we are inserting <strong>decompress </strong>value of First Name from the
Compressed table.<br />
Now, let’s check compress & decompress table data<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-4d0i4PV8RkA/WAOgKyyUUpI/AAAAAAAAD2s/rMxLL80NtKMRJMFMtiBJXaYAvNMG3D8-wCEw/s1600/Check_Compressed_Decompress_Data.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Decompress function " border="0" height="322" src="https://1.bp.blogspot.com/-4d0i4PV8RkA/WAOgKyyUUpI/AAAAAAAAD2s/rMxLL80NtKMRJMFMtiBJXaYAvNMG3D8-wCEw/s400/Check_Compressed_Decompress_Data.png" title="" width="400" /></a></div>
<br />
<br />
Now, Your might thinking that the output of both compress and decompress is
not readable.<br />
So you are right to make data readable of Decompress table we need to
<strong>type cast</strong>. <br />
See below snap for same.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-pvZ98IFVYtg/WAOgK79pi4I/AAAAAAAAD2o/rmyQ52nIMYYl9qNSKC3vRYP84iHvfGvwwCEw/s1600/Decompressed_value.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Decompress type casting" border="0" height="246" src="https://1.bp.blogspot.com/-pvZ98IFVYtg/WAOgK79pi4I/AAAAAAAAD2o/rmyQ52nIMYYl9qNSKC3vRYP84iHvfGvwwCEw/s400/Decompressed_value.png" title="" width="400" /></a></div>
<br />
<br />
Till now we know how to use this <strong>Compress & Decompress
function</strong>. Now, let me share the benefit of using Compress. if you see
below snap you will find that <strong>data length of compress</strong> is
comparatively less than normal and decompressed data length .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-oHNVaC_56eE/WAOgLMJGUeI/AAAAAAAAD2w/YXCdXwRX-rk79Oa0rdy_jd-w9eWYcvP1gCEw/s1600/DataLength_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Datalength in compress data" border="0" height="297" src="https://1.bp.blogspot.com/-oHNVaC_56eE/WAOgLMJGUeI/AAAAAAAAD2w/YXCdXwRX-rk79Oa0rdy_jd-w9eWYcvP1gCEw/s400/DataLength_Indiandotnet.png" title="" width="400" /></a></div>
<br />
<br />
Obviously, <strong>compression</strong> helps you somewhere in the overall
performance of your application. <br />
The good point is you can pass the compress data to your .net application
and decompress using GzipStream as well.<br />
<br />
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.<br />
<br />
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..<br />
<br />
I hope you will be excited in using this function.<br />
<br />
Please, share your input.<br />RJ!</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com2tag:blogger.com,1999:blog-6538321000057298497.post-41790731193168032312016-10-15T10:47:00.001-07:002016-10-15T10:47:54.450-07:00Here Come New Ideas for DROP IF EXISTS in SQL SERVER<div dir="ltr" style="text-align: left;" trbidi="on">
In the <strong>Series of SQL SERVER 2016 journey</strong>, this is our new
article. In this article, we are sharing a new cool feature which introduced in
<strong>SQL SERVER 2016</strong> which is <strong>DROP IF EXISTS (DIE)
.</strong><br />
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<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-LJ2dVYdjWe0/WAJrJE43UrI/AAAAAAAAD2U/W5eyZfyZTKwB_tdhyoPG2f2HRgzFrejLACLcB/s1600/TraditionalWay_Indiandotnet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="120" src="https://1.bp.blogspot.com/-LJ2dVYdjWe0/WAJrJE43UrI/AAAAAAAAD2U/W5eyZfyZTKwB_tdhyoPG2f2HRgzFrejLACLcB/s400/TraditionalWay_Indiandotnet.png" width="400" /></a></div>
<br />
Now, in SQL SERVER 2016 the same task is super easy. You can write the
following syntax to <strong>drop the table</strong> object <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://3.bp.blogspot.com/-BUWMlhe84RA/WAJrJIPEFjI/AAAAAAAAD2Y/1KpJqrWT3_AlBG0AhcFtCJRC0azMLRoqACEw/s1600/DROP_TABLE_IF_EXISTS_INDIANDOTNET.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="83" src="https://3.bp.blogspot.com/-BUWMlhe84RA/WAJrJIPEFjI/AAAAAAAAD2Y/1KpJqrWT3_AlBG0AhcFtCJRC0azMLRoqACEw/s400/DROP_TABLE_IF_EXISTS_INDIANDOTNET.png" width="400" /></a></div>
<br />
<strong>DROP TABLE IF EXISTS TABLENAME </strong><br />
The best part is if suppose the object does not exist then here will be no
error execution will continue.<br />
Let me share one more example of Dropping a stored procedure.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://1.bp.blogspot.com/-4myb9AJWihk/WAJrJJotxGI/AAAAAAAAD2Q/xPZTiyk6pig1V4oE4GOGhU5m8OoO9UNrwCEw/s1600/DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="93" src="https://1.bp.blogspot.com/-4myb9AJWihk/WAJrJJotxGI/AAAAAAAAD2Q/xPZTiyk6pig1V4oE4GOGhU5m8OoO9UNrwCEw/s400/DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET.png" width="400" /></a></div>
<br />
Similar, way we can write for following data objects and with the following
syntax<br />
<br />
<strong>Procedure:- </strong>DROP PROCEDURE IF EXISTS Procedure Name<br />
<br />
<strong>Assembly:- </strong><br />DROP ASSEMBLY IF EXISTS Assembly Name<br />
<br />
<strong>ROLENAME :- </strong><br />DROP ROLE IF EXISTS ROLENAME<br />
<br />
<strong>TRIGGER :-</strong> <br />DROP TRIGGER IF EXISTS Trigger Name<br />
<br />
<strong>VIEW:-</strong><br />DROP VIEW IF EXISTS View Name<br />
<br />
<strong>RULE:- </strong><br />DROP RULE IF EXISTS RULENAME\<br />
<br />
<strong>Type:-</strong><br />DROP TYPE IF EXISTS Type Name<br />
<br />
<strong>Database:- </strong>DROP DATABASE IF EXISTS Database Name<br />
<br />
<b>Schema:-</b><br />DROP SCHEMA IF EXISTS Schema Name<br />
<br />
<strong>User:- </strong><br />DROP USER IF EXISTS Username<br />
<br />
<strong>SECURITY POLICY:-</strong><br />DROP SECURITY POLICY IF EXISTS Policy
Name<br />
<br />
<strong>View :- </strong><br />DROP VIEW IF EXISTS View Name<br />
<br />
<strong>FUNCTION:-</strong><br />DROP FUNCTION IF EXISTS Function Name<br />
<br />
<strong>SEQUENCE:-</strong><br />DROP SEQUENCE IF EXISTS Sequence Name<br />
<br />
<br />
<br />
<strong>Synonym:- </strong><br />DROP SYNONYM IF EXISTS Synonym Name<br />
<br />
I like this feature I am sure you will also like this.<br />
<br />
Please, do share your feedback for blog post.<br />
Enjoy !!</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-78561524125913587152016-10-11T01:24:00.000-07:002016-10-11T01:24:10.795-07:009 Amazing features of SQL SERVER 2016<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
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.
<br />
Anyways, so you all might aware that Microsoft launched <strong>SQL SERVER
2016</strong> officially in June 2016.
<br />
You can download the SQL Server 2016 via <a href="https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2016">Link</a>.
<br />
Obviously, this is a new revolution in SQL SERVER series. You will find many
great features in this version.
<br />
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.
<br />
<br />
<strong>1) JSON in SQL SERVER :-</strong>
<br />
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<strong> JSON in SQL SERVER
2016.</strong>
<br />
<strong></strong>
<br />
<strong>2)Always Encrypted :-</strong>
<br />
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
<strong>always in encrypted format and SQL server</strong> 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 ?
<br />
<br />
<strong>3) Row Level Security :-</strong>
<br />
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<strong> Row Level Security</strong>.
<br />
<br />
<strong>4) “R” in SQL SERVER :-</strong>
<br />
For the data scientist, it is a great NEWS. As <strong>Resolution
Analytics</strong> is purchased by <strong>Microsoft</strong> and It is
incorporated in SQL SERVER. You can run R analysis query in SQL Server.
<br />
<br />
<strong>5) Temporal Table:-</strong>
<br />
The <strong>Temporal table holds</strong> 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.
<br />
<br />
<strong>6) PolyBase :-</strong>
<br />
With the help of this feature, you can access data which exist in
<strong>Azure Blob or Hadoop cluster</strong> 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.
<br />
<br />
<strong>7) Stretch Database:-</strong>
<br />
I am pretty much sure by the name you can guess this feature. So, with the
<strong>Stretch database</strong> 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.
<br />
<br />
<strong>8)Query store :-</strong>
<br />
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.
<br />
<br />
<strong>9) Mobile report:-</strong>
<br />
<strong></strong>As mentioned earlier this is the revolution in SQL SERVER
2016. In SSRS there are many important changes introduced. Now we can import
<strong>Power BI report in SSRS</strong> and apart from this you can create a
mobile report which you can run on Mobile.
<br />
<br />
Now, we started officially <strong>SQL SERVER 2016 tutorial series.</strong>
<br />
<br />
Moving forward we are going to discuss all these features in details and also
the couple of new things which <strong>introduced in SQL SERVER 2016.</strong>
<br />
<br />
enjoy.
<br />
Happy VijayDashmi. <br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com2tag:blogger.com,1999:blog-6538321000057298497.post-11268379490013732692016-03-29T08:59:00.002-07:002016-03-29T08:59:34.800-07:00MS Build 2016 Live StreamingWatch Live streaming of MS BUILD 2016
<div dir="ltr" style="text-align: left;" trbidi="on">
<style type="text/css"> iframe#videoPlayerIframe { background-color: white; /* remove or change this declaration to look nice on the parent site */ border: 0px solid white; height: 1000px; /* this is a guess of the default height of the embedded page */ overflow: hidden; padding: 0px; width: 780px; /* change this declaration to look nice on the parent site */ } </style>
<script type="text/javascript"> (function($) { var height, newHeight; $(window).on('message', function(e){ try{ newHeight = e.originalEvent.data; if(newHeight && (height != newHeight)){ $('#videoPlayerIframe').height(newHeight); height = newHeight; } }catch(e){} }); })(jQuery); </script>
<iframe src="https://channel9.msdn.com/LiveEmbedPlayer/Build2016?wt.mc_id=DX_835177" seamless="seamless" frameborder="0" marginheight="0" marginwidth="0" id="videoPlayerIframe" allowfullscreen></iframe>
<br /></div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-23891056409133019992015-12-20T01:02:00.001-08:002015-12-20T01:02:14.749-08:00The Unconventional Guide to How to Implement Lookup Transformations TIP #124<div dir="ltr" style="text-align: left;" trbidi="on">
This is one the articles in the series of <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">step by step SSIS tutorial</a>. In this post, we are going to
understand <strong>Lookup transformation</strong> and also see an example to
implement <strong>Lookup transformation in 8 easy steps</strong>.<br />
Now, <strong>Lookup transformation</strong> is one of the interesting<strong>
transformation</strong> which is used to implement <strong>equijoin</strong>
between data sources.<br />We are discussing the same example which we discuss in
the earlier post which is <strong>Person & PersonPone tables of
AdventureWorks.</strong><br />
Suppose, we want <strong>Person details and the phone number of
person</strong> as a result then we can apply <strong>Lookup
transformation</strong>. <br />
When, we applied the <strong>Lookup transformation</strong> between
<strong>Person & Person Phone table</strong> then we will get two result
set.<br />
<strong>One of the result sets is the Matched result</strong> set in which we
will get the <strong>Person data and the First Matched Phone number</strong>
while the PersonPhone table might have multiple phones for a person but we will
get only one phone number record because<strong> Lookup transformation</strong>
uses <strong>EquiJoin</strong>.<br />
In the <strong>second result set, we will get unmatched Person
records</strong> which mean the Person who doesn’t has a Phone number in
PersonPhone table.<br />
I think, we have a basic understanding of <strong>what is Lookup
Transformation</strong>. Now, we are going to <strong>implement Lookup
Transformation step by step.</strong><br />
<strong>Step 1:-</strong><br />So, the first basic step is to add a package and
drag-drop data flow task as shown in below figure. You can rename the data flow
task as I did in below figure.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-rSBRwTsudxI/VnZty10oUbI/AAAAAAAADuk/I5Ab8HSF_HY/s1600/1_DataSource.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="170" src="http://4.bp.blogspot.com/-rSBRwTsudxI/VnZty10oUbI/AAAAAAAADuk/I5Ab8HSF_HY/s400/1_DataSource.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 2: - In this step drag drop a data source and configure it for Person
table as shown in below figure. If you face any difficulties in this then you
can review previous articles of this series<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-aMcF5QH5NpI/VnZtzSV3JEI/AAAAAAAADu4/SsZs7T5iipI/s1600/2_SqlSource.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="386" src="http://1.bp.blogspot.com/-aMcF5QH5NpI/VnZtzSV3JEI/AAAAAAAADu4/SsZs7T5iipI/s400/2_SqlSource.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 3:- Once the Person table is configured just drag drop <strong>Lookup
Transformation</strong> control and provide the input of Person table Data
source to Lookup table. When you try to configure the Lookup you will get the
following screen. We will discuss <strong>Cache mode</strong> separately in
detail. In this example, we are using Full Cache and connection type to OLEDB
connection Manager.<br />
Now, Click on Connection option on the right and configure the PersonPhone
table.<br /><a href="https://www.blogger.com/null"><br /></a><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-K9pb8cIi0bw/VnZtzn3LEII/AAAAAAAADu0/_VHhj_HtYZs/s1600/4_Lookup.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="390" src="http://3.bp.blogspot.com/-K9pb8cIi0bw/VnZtzn3LEII/AAAAAAAADu0/_VHhj_HtYZs/s400/4_Lookup.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-HcU665GOIyY/VnZt0Y4n-4I/AAAAAAAADvQ/Q46m_T3prQo/s1600/5_LookupTransformationColumn.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="391" src="http://4.bp.blogspot.com/-HcU665GOIyY/VnZt0Y4n-4I/AAAAAAAADvQ/Q46m_T3prQo/s400/5_LookupTransformationColumn.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 4:- Now, configure the Columns relationship between Person &
PersonPhone table.To achieve this just drag-drop BusinessEntityId of<strong>
available input columns</strong> of Person table to <strong>available
Lookup columns</strong> which are in PersonPhone table and matched with
BusinessEntityId which is common between Person & PersonPhone table. Here we
have to select PhoneNumber column which we have to include in the result
set.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-qy6-n4sH-hk/VnZt0CKuADI/AAAAAAAADvE/iym-NTc6hz8/s1600/4_Lookup_111.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="393" src="http://1.bp.blogspot.com/-qy6-n4sH-hk/VnZt0CKuADI/AAAAAAAADvE/iym-NTc6hz8/s400/4_Lookup_111.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 5:- Now, Drag drop two excel destination source and configure it. As, I
did in below figures. Here one Excel is with name LookupOutput.xls which contain
matched records.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-yZJQb2EUbF8/VnZt09438VI/AAAAAAAADvc/Db8marZNQ3M/s1600/7_ExcelOutput.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="255" src="http://2.bp.blogspot.com/-yZJQb2EUbF8/VnZt09438VI/AAAAAAAADvc/Db8marZNQ3M/s400/7_ExcelOutput.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Below, is another Excel which contains unmatched records. Here UnMatched
means the Person records who don’t have phone numbers.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-ZZ6-ZJ_8gio/VnZt1UJmJRI/AAAAAAAADvk/7y59Q_PpH4Y/s1600/8_Lookupoutputnotmatched.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="257" src="http://3.bp.blogspot.com/-ZZ6-ZJ_8gio/VnZt1UJmJRI/AAAAAAAADvk/7y59Q_PpH4Y/s400/8_Lookupoutputnotmatched.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 6:- Now, If you see there are 3 output lines coming from Lookup
transformation.So, First blue output gives it to Lookupoutput excel which will
have all the matched records of person & Person Phone number. You can
configure inputs as shown below.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-lmNeW7CV7vc/VnZt19fgrvI/AAAAAAAADvo/mrfsRbTXcxY/s1600/9_Matched_Excel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="397" src="http://1.bp.blogspot.com/-lmNeW7CV7vc/VnZt19fgrvI/AAAAAAAADvo/mrfsRbTXcxY/s400/9_Matched_Excel.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 7:- In a similar way, we have to configure unmatched record output.
Once, we done with configuration of both Matched & unmatched output, we will
get following structure as shown in below screen<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-gcoSaDUnO8w/VnZtyMPNxuI/AAAAAAAADuw/fWvFCcF1DNw/s1600/10_NotMatched_Excel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="388" src="http://3.bp.blogspot.com/-gcoSaDUnO8w/VnZtyMPNxuI/AAAAAAAADuw/fWvFCcF1DNw/s400/10_NotMatched_Excel.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-f2Rm6n4GdMs/VnZtx5WnnaI/AAAAAAAADuU/6GCcLw8k8-g/s1600/10_Final.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="278" src="http://2.bp.blogspot.com/-f2Rm6n4GdMs/VnZtx5WnnaI/AAAAAAAADuU/6GCcLw8k8-g/s400/10_Final.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 8:- Once, everything is configured you can run the package and if
everything is perfect then you will get the following screen<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-fgokoWfZrac/VnZt2AE7mjI/AAAAAAAADvs/QyAxq4seDEs/s1600/finalRun.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="263" src="http://1.bp.blogspot.com/-fgokoWfZrac/VnZt2AE7mjI/AAAAAAAADvs/QyAxq4seDEs/s400/finalRun.JPG" width="400" /></a></div>
<a href="http://4.bp.blogspot.com/-ZU_8PjX92Vo/VnZtx8ufEdI/AAAAAAAADuA/Ke918jSBcmA/s1600/11_Result_Run.JPG" style="margin-left: 1em; margin-right: 1em;"><br /></a><a href="https://www.blogger.com/null"></a>
<strong>I hope above steps are easy and you can implement Lookup
Transformation at your end too.</strong><br />
Please, provide your inputs.<br />
Enjoy !!!<br />
RJ!!!<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-ZU_8PjX92Vo/VnZtx8ufEdI/AAAAAAAADuA/Ke918jSBcmA/s1600/11_Result_Run.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-66584189029568599062015-12-18T00:46:00.000-08:002015-12-18T00:46:17.846-08:00How to do Step by Step Merge Join Transformations - #123<div dir="ltr" style="text-align: left;" trbidi="on">
In last post tip <a href="http://sqlraaga.blogspot.in/2015/12/step-by-step-merge-transformation.html" target="_blank">#122</a>, We discussed <strong>Merge transformation</strong>. Now
in this tip, we are going to discuss <strong>Merge Join</strong>. Although, it
might be confusing sometimes <strong>Merge & Merge Join</strong>. So, let me
explain it here.<br />
As explain earlier merge is like a union operation in which it just collect
all the records from provided sources without any condition. suppose if we have
Person & PersonPhones two sources as shown below <br />
<strong>Below is Person table with two columns personId &
Name</strong><br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 368px;">
<tbody>
<tr>
<td valign="top" width="198"><strong>PersonId</strong></td>
<td valign="top" width="168"><strong>Name</strong></td></tr>
<tr>
<td valign="top" width="197">1</td>
<td valign="top" width="169">Ram</td></tr>
<tr>
<td valign="top" width="197">2</td>
<td valign="top" width="169">Shyam</td></tr>
<tr>
<td valign="top" width="197">3</td>
<td valign="top" width="170">Ghanshyam</td></tr>
</tbody></table>
<strong>Below is PersonPhones table with two columns PersonId &
Phone</strong><br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 400px;">
<tbody>
<tr>
<td valign="top" width="200">PersonId</td>
<td valign="top" width="200">Phone</td></tr>
<tr>
<td valign="top" width="200">1</td>
<td valign="top" width="200">9999</td></tr>
<tr>
<td valign="top" width="200">2</td>
<td valign="top" width="200">3122</td></tr>
<tr>
<td valign="top" width="200">3</td>
<td valign="top" width="200">3422</td></tr>
</tbody></table>
Now if we talk about merge transformation then the result would be as shown
below<br />
<strong>Merge Transformation output</strong><br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 400px;">
<tbody>
<tr>
<td valign="top" width="133"><strong>PersonId</strong></td>
<td valign="top" width="133"><strong>Name</strong></td>
<td valign="top" width="133"><strong>Phone</strong></td></tr>
<tr>
<td valign="top" width="133">1</td>
<td valign="top" width="133">Ram</td>
<td valign="top" width="133"> </td></tr>
<tr>
<td valign="top" width="133">2</td>
<td valign="top" width="133">Shyam</td>
<td valign="top" width="133"> </td></tr>
<tr>
<td valign="top" width="133">3</td>
<td valign="top" width="133">Ghanshyam</td>
<td valign="top" width="133"> </td></tr>
<tr>
<td valign="top" width="133">1</td>
<td valign="top" width="133"> </td>
<td valign="top" width="133">9999</td></tr>
<tr>
<td valign="top" width="133">2</td>
<td valign="top" width="133"> </td>
<td valign="top" width="133">3122</td></tr>
<tr>
<td valign="top" width="133">3</td>
<td valign="top" width="133"> </td>
<td valign="top" width="133">3422</td></tr>
</tbody></table>
Now, If we talk about <strong>Merge Join transformation </strong>for above
same inputs (<strong>Person, PersonPhones</strong>) then we will get following
output<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 400px;">
<tbody>
<tr>
<td valign="top" width="133"><strong>PersonId</strong></td>
<td valign="top" width="133"><strong>Name</strong></td>
<td valign="top" width="133"><strong>Phone</strong></td></tr>
<tr>
<td valign="top" width="133">1</td>
<td valign="top" width="133">Ram</td>
<td valign="top" width="133">9999</td></tr>
<tr>
<td valign="top" width="133">2</td>
<td valign="top" width="133">Shyam</td>
<td valign="top" width="133">3122</td></tr>
<tr>
<td valign="top" width="133">3</td>
<td valign="top" width="133">GhanShyam</td>
<td valign="top" width="133">3422</td></tr>
</tbody></table>
So, <strong>Merge Join is basically a join like Inner join, Left outer join,
Full outer join etc.</strong><br />
I hope you got the context. Here we go step by step to implement the
<strong>Merge join transformation</strong><br />
<strong>Step 1:-</strong> Add the different sources which you want. For this
example I am taking two sources which have AdventureWorks database and one
source have Person table & another one have PersonPhone as shown above in
the example. <br />
We added data flow task as shown in below image and renamed it to
<strong>Merge join</strong> <strong>data flow task</strong>.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-A5azNH5gc8M/VnPGbDRQqII/AAAAAAAADsg/VNTNbHvpXLE/s1600/DataflowTaskStep1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="129" src="http://4.bp.blogspot.com/-A5azNH5gc8M/VnPGbDRQqII/AAAAAAAADsg/VNTNbHvpXLE/s320/DataflowTaskStep1.JPG" width="320" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Now, in this data flow task, we will add data sources, in the first source we
use <strong>person table</strong> and in the second source we use
<strong>PersonPhone</strong>.<br />
from person table, we are choosing BusinessEntityId, FirstName,MiddleName,
LastName,suffix as shown in below figure<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-uN19LiXLou0/VnPGc8YNG5I/AAAAAAAADts/mZ6UUx3oplQ/s1600/MergeSource1Step2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="309" src="http://4.bp.blogspot.com/-uN19LiXLou0/VnPGc8YNG5I/AAAAAAAADts/mZ6UUx3oplQ/s320/MergeSource1Step2.JPG" width="320" /></a></div>
<a href="https://www.blogger.com/null"></a> <br />
In the other source, we are using BusinessEntityId & Phone number
columns.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-BVBiUkR_NUk/VnPGdLvr0qI/AAAAAAAADtk/o298oFoyBnA/s1600/MergeSource2Step3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://1.bp.blogspot.com/-BVBiUkR_NUk/VnPGdLvr0qI/AAAAAAAADtk/o298oFoyBnA/s320/MergeSource2Step3.JPG" width="320" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
If you find any issue in above steps then please, follow previous
<strong>articles of step by step SSIS on Indiandotnet</strong>.<br />
Step 2:- Now once the source is configured, we are going to drag drop merge
control as shown in below figure.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-MCdI4dH898s/VnPGbFx2w6I/AAAAAAAADs8/1RvM_RcQSVE/s1600/DragDropMergejoinStep4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="91" src="http://4.bp.blogspot.com/-MCdI4dH898s/VnPGbFx2w6I/AAAAAAAADs8/1RvM_RcQSVE/s400/DragDropMergejoinStep4.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 3:- Now to configure <strong>Merge join, </strong>we are going to drag
drop inputs from the sources. When you try this at your end then you will get
the following screen. Here you have to set <strong>left input and right
input</strong> for the <strong>merge join</strong>.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-wypDevVLfkw/VnPGaYeVOeI/AAAAAAAADso/Wxp2Kp_e9Tw/s1600/ConfigureMergeJoin4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="183" src="http://3.bp.blogspot.com/-wypDevVLfkw/VnPGaYeVOeI/AAAAAAAADso/Wxp2Kp_e9Tw/s320/ConfigureMergeJoin4.JPG" width="320" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Now, When you tried it you might found <strong>following warning</strong>.
Which means the provided inputs of datasource1 & datasource2 are not
sorted.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-GQedYXTVE-8/VnPGdgvpxWI/AAAAAAAADto/RMVTyBBG1U0/s1600/WarningforSortStep5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="107" src="http://3.bp.blogspot.com/-GQedYXTVE-8/VnPGdgvpxWI/AAAAAAAADto/RMVTyBBG1U0/s400/WarningforSortStep5.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 4:- Here you have the option to add a <strong><a href="http://sqlraaga.blogspot.in/2015/09/how-to-use-sort-control-in-ssis-tip-115.html" target="_blank">Sort transformation</a></strong> and then provide input to
<strong>merge join transformation</strong> control which is fairly simple and we
have discussed in previous tips. Here, I am sharing one more option. <br />
In this, you have to right click on DataSource and choose option
A<strong>dvance Editor</strong> by which you will get the following screen. You
have to select “<strong>OLEDB Source output</strong>” of “<strong>Input and
output Properties</strong>” tab.<br />
Here, we need to make the <strong>IsSorted</strong> property to
<strong>True</strong> as highlighted in below snap.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-ehTPdfI-1lI/VnPGbwyOzDI/AAAAAAAADtY/TasvMPUtLmk/s1600/IsSortedProblem6.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-ehTPdfI-1lI/VnPGbwyOzDI/AAAAAAAADtY/TasvMPUtLmk/s320/IsSortedProblem6.JPG" width="301" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-FFcGwQMIeDw/VnPGaYKoDoI/AAAAAAAADt0/87ykuKVYpwk/s1600/BusinessEntitySortKEy.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 5:- Once the above Step is done next step is click on output column’s
BusinessEntityId value. Here you have to set the
<strong>SortKeyPosition</strong> to 1.<br />
As highlighted in below screen.<br />
<a href="http://2.bp.blogspot.com/-FFcGwQMIeDw/VnPGaYKoDoI/AAAAAAAADt0/87ykuKVYpwk/s1600/BusinessEntitySortKEy.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-FFcGwQMIeDw/VnPGaYKoDoI/AAAAAAAADt0/87ykuKVYpwk/s320/BusinessEntitySortKEy.JPG" width="302" /></a> <br />
<a href="https://www.blogger.com/null"><br /></a>
Step 6:- Once you configured sorted input sources then next step is to
configure the <strong>merge join. </strong>So when you click on Edit option of
merge join. You will get the following screen. <br />
Here if you see, we can configure join type like <strong>Inner join, Left
outer join, Full outer join. </strong><br />
For<strong> </strong>current example, we are choosing the option
<strong>“Inner join”</strong> and joining key is BusinessEntityId which is
primary key in Person table and foreign key in PersonPhone table. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-6wimoQrhe9o/VnPGbD_G-FI/AAAAAAAADtA/KtQLLGOuX5Y/s1600/ConfigureMergejoinTypesStep6.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="http://3.bp.blogspot.com/-6wimoQrhe9o/VnPGbD_G-FI/AAAAAAAADtA/KtQLLGOuX5Y/s400/ConfigureMergejoinTypesStep6.JPG" width="377" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Once the configuration is done. We can select what are the columns which we
need as an output.<br />
Step 7:- Now, Next step is to configure the output in excel. for this, we
drag & drop a destination source. In the current example, we are using Excel
as a destination. We are mapping the columns of output to excel as shown in
below figures.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-mrX36pjb5LA/VnPGaQdsRbI/AAAAAAAADss/UYYn-CdGD9k/s1600/ConfigureExcelDestination.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="http://1.bp.blogspot.com/-mrX36pjb5LA/VnPGaQdsRbI/AAAAAAAADss/UYYn-CdGD9k/s400/ConfigureExcelDestination.JPG" width="382" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-6BLV2fWiZMs/VnPGb9te6kI/AAAAAAAADtE/Pr_p5OrepBc/s1600/MappingInexecel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="387" src="http://3.bp.blogspot.com/-6BLV2fWiZMs/VnPGb9te6kI/AAAAAAAADtE/Pr_p5OrepBc/s400/MappingInexecel.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
Step 8:- Once, everything is configured. We run the package and if everything
is working fine then we will get the following screen.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-0T0GqjYh4oM/VnPGbz4QyII/AAAAAAAADtI/xvZp41HI9gg/s1600/MergeJoinRunResult.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="276" src="http://1.bp.blogspot.com/-0T0GqjYh4oM/VnPGbz4QyII/AAAAAAAADtI/xvZp41HI9gg/s400/MergeJoinRunResult.JPG" width="400" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
To cross check the result we open the excel and we get the output which we
expected as shown in below figure.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-vf6MGpPFcw4/VnPGdCfoaII/AAAAAAAADtg/_-01mfsgzeo/s1600/ResultOutputinExcel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="188" src="http://4.bp.blogspot.com/-vf6MGpPFcw4/VnPGdCfoaII/AAAAAAAADtg/_-01mfsgzeo/s320/ResultOutputinExcel.JPG" width="320" /></a></div>
<a href="https://www.blogger.com/null"><br /></a>
I hope this article might help you to understand the <strong>Merge
join.</strong><br />
Enjoy !!!<br />
RJ!!</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com5tag:blogger.com,1999:blog-6538321000057298497.post-72118948582916642562015-12-13T07:52:00.001-08:002015-12-13T07:52:13.760-08:00SQL Server tips ,SQL Server tutorials, database tips- Indiandotnet: Step by Step Merge transformation control in SSIS ...<a href="http://sqlraaga.blogspot.com/2015/12/step-by-step-merge-transformation.html?spref=bl">SQL Server tips ,SQL Server tutorials, database tips- Indiandotnet: Step by Step Merge transformation control in SSIS ...</a>: In the Series of Zero to hero in SSIS this is another post. In this post we are going to discuss M erge transformation control. ...Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-49277805205040556062015-12-13T07:51:00.000-08:002015-12-13T07:51:24.320-08:00Step by Step Merge transformation control in SSIS TIP #122<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
In the <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">Series of Zero to hero in SSIS</a><b> </b> this is
another post. In this post we are going to discuss M<b>erge
transformation</b> control.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
By the name it is clear that <b>Merge transformation</b> do some
<b>merge</b> related task. For example suppose there is two sources
which are source1 </div>
<div style="text-align: justify;">
and source2. Now we want merge records of Source1 & Source2 then this
transformation is helpful.</div>
<div style="text-align: justify;">
Although, sometimes you might confuse that there is already <b>UNION ALL
</b>transformation the when to use <b>UNION ALL</b> or
<b>Merge transformation</b>.</div>
<div style="text-align: justify;">
For Merge transformation we require <b>sorted inputs</b> and there
is <b>only two inputs required</b>. The output of<b> merge
transformation will be sorted output.</b></div>
<div style="text-align: justify;">
Let’s start it <b>Step by Step</b> </div>
<div style="text-align: justify;">
Step 1:- Firstly configure the sources. In current example I am using
AdventureWorks2008 database’s person table and using FirstName, LastName,
MiddleName columns as a Source1. For Source2 I am using AdventureWorks2012
database’s person table and using FirstName, LastName, MiddleName again. (You
can choose any different source like flat file, excel ,other data source
provider as well to make it simple I am using same source)</div>
<div style="text-align: justify;">
<a href="https://indiandotnet.files.wordpress.com/2015/12/source1.jpg"><img alt="source1" border="0" height="484" src="https://indiandotnet.files.wordpress.com/2015/12/source1.jpg" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="source1" width="496" /></a></div>
<div style="text-align: justify;">
Step 2:- Once you setup your data source the next mandatory condition is the
output should be sorted. So here we are using<b> Sort transformation
control. </b>Now for first source we are sorting data on
FirstName,MiddleName, LastName.</div>
<div style="text-align: justify;">
<a href="https://www.blogger.com/null"><img alt="Sort1" border="0" height="480" src="https://indiandotnet.files.wordpress.com/2015/12/sort2.jpg" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="Sort1" width="644" /></a></div>
<div style="text-align: justify;">
and for second source we are sorting data on FirstName , LastName
,MiddleName</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://3.bp.blogspot.com/-0OxFrg4Y7_s/Vm2R_ISjBiI/AAAAAAAADqs/L15idK2fIbo/s1600/Sort2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="306" src="http://3.bp.blogspot.com/-0OxFrg4Y7_s/Vm2R_ISjBiI/AAAAAAAADqs/L15idK2fIbo/s400/Sort2.JPG" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Step 3: Now once we have two sorted input we are going to use Merge
transformation control so just drag drop and configure it. As shown in below
figure. So there are two inputs which is Merge Input 1 & Merge Input 2 and
we configured the data according to our need. And if you see below screen you
will find “<b>Configure the properties used to merge two sorted inputs into
one output</b>” which means for Merge transformation we require two
<b>sorted inputs only.</b></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-WxlJ4rG9Wqs/Vm2SViLzB0I/AAAAAAAADrM/kM6ftNbeMI8/s1600/Merge_configuration.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="http://4.bp.blogspot.com/-WxlJ4rG9Wqs/Vm2SViLzB0I/AAAAAAAADrM/kM6ftNbeMI8/s640/Merge_configuration.JPG" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://2.bp.blogspot.com/-R-I1yMNjumw/Vm2SPhilrFI/AAAAAAAADq8/BERaww9DZ48/s1600/SortinputRequired.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="224" src="http://2.bp.blogspot.com/-R-I1yMNjumw/Vm2SPhilrFI/AAAAAAAADq8/BERaww9DZ48/s640/SortinputRequired.JPG" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
Step 4:- Now,we want the output of merge transformation in an excel so, here
dropped Excel destination and configure it.So you can see below configured
package with excel destination.</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-63gs6Eo3ETc/Vm2SXYertNI/AAAAAAAADrY/U9e--2S6O4Y/s1600/Merge_Final_dtsx.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="414" src="http://4.bp.blogspot.com/-63gs6Eo3ETc/Vm2SXYertNI/AAAAAAAADrY/U9e--2S6O4Y/s640/Merge_Final_dtsx.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://4.bp.blogspot.com/-kABdTl0Rb5E/Vm2TCan4byI/AAAAAAAADro/OnJ5RdObYhU/s1600/outputexcel.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="435" src="http://4.bp.blogspot.com/-kABdTl0Rb5E/Vm2TCan4byI/AAAAAAAADro/OnJ5RdObYhU/s640/outputexcel.JPG" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
Step 5:- Now just hit F5 or run it. If everything configured properly then
you will get all the green checks with no of rows count at each level as shown
in below figure</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="http://3.bp.blogspot.com/-tsbk8jPYOPs/Vm2SWU4x5xI/AAAAAAAADrQ/Q7QOcV99Vo0/s1600/Merge_dtsx_Result.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="360" src="http://3.bp.blogspot.com/-tsbk8jPYOPs/Vm2SWU4x5xI/AAAAAAAADrQ/Q7QOcV99Vo0/s640/Merge_dtsx_Result.JPG" width="640" /></a><a href="http://4.bp.blogspot.com/-kABdTl0Rb5E/Vm2TCan4byI/AAAAAAAADro/OnJ5RdObYhU/s1600/outputexcel.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
Step 6:- We can cross check Excel also so in my case we got the required data
as shown in below Excel's snapshot.</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="https://www.blogger.com/null" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/null" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/null" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/null" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://www.blogger.com/null" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="http://2.bp.blogspot.com/-yMrbQOuNirM/Vm2TV77sRYI/AAAAAAAADrw/Hmg43jVFBN8/s1600/Merge_Result.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="420" src="http://2.bp.blogspot.com/-yMrbQOuNirM/Vm2TV77sRYI/AAAAAAAADrw/Hmg43jVFBN8/s640/Merge_Result.JPG" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
</div>
<div style="text-align: justify;">
So, Isn’t it simple ?</div>
<div style="text-align: justify;">
Here the important points to remember </div>
<div style="text-align: justify;">
<b>1) Merge requires Sorted inputs only </b></div>
<div style="text-align: justify;">
<b>2) Merge requires only two inputs</b></div>
<div style="text-align: justify;">
<b>I hope this tutorial might help you. I appreciate your feed</b>back.</div>
<div style="text-align: justify;">
Enjoy !!</div>
<div style="text-align: justify;">
RJ!!</div>
</div>
Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-28514422785133374762015-10-18T10:56:00.000-07:002015-10-18T11:58:03.560-07:00Step by Step SSIS Multicast Transformation TIP #121<p>Dear Friends,</p> <p>This is another post in the series of <strong>step by step SSIS</strong> ,<strong>Zero to hero series</strong>. In this post we will discuss another transformation control which is <strong>Multicast transformation.</strong></p> <p><strong>Multicast</strong> is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say <strong>multicast</strong> transformation create <strong>multiple pipelines for the same data.</strong></p> <p>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.</p> <p>Now, here we go <strong>step by step</strong>. for your information in this example we are going to use similar example which we did in last post of <a href="http://sqlraaga.blogspot.in/2015/10/step-by-step-ssisderived-column.html" target="_blank">Derived column Transformation tip #120</a></p> <p>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 </p> <p><a href="http://lh3.googleusercontent.com/-NQ5k93iFxcs/ViPrkeqR2wI/AAAAAAAADm4/1_7vA9Un7GI/s1600-h/Data1%25255B9%25255D.jpg"><img title="Data1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Data1" src="http://lh3.googleusercontent.com/-TTxKbrIT3HQ/ViPrlTmiDeI/AAAAAAAADnA/SaesG4cDNNY/Data1_thumb%25255B7%25255D.jpg?imgmax=800" width="538" height="484"></a></p> <p>Step 2:- Once the data is configured. Drag drop <strong>Multicast</strong> control as shown in below figure and give output of source object to <strong>Multicast</strong> and try to configure it.</p> <p><a href="http://lh3.googleusercontent.com/-JKbmbnh_mcU/ViPrmFybsiI/AAAAAAAADnE/nKGWCGC1IiE/s1600-h/DragDropMulticast%25255B8%25255D.jpg"><img title="DragDropMulticast" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="DragDropMulticast" src="http://lh3.googleusercontent.com/-eXIwvkYh6vk/ViPrnXbcZfI/AAAAAAAADnQ/OrPM_W3YXsc/DragDropMulticast_thumb%25255B6%25255D.jpg?imgmax=800" width="644" height="260"></a></p> <p>Step 3:- Now, when we have <strong>configured Multicast</strong> we can get multiple output from <strong>Multicast</strong> 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 <strong>drag drop derived column</strong> and configure then according to 10% ,20% WAT tax for reference you can take a look of <a href="https://indiandotnet.wordpress.com/2015/10/18/step-by-step-ssisderived-column-transformation-tip-120/" target="_blank">tip #120</a> </p> <p>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 </p> <p>1) with 10% ,with 20 % and without and tax </p> <p><a href="http://lh3.googleusercontent.com/-qrAVMJ7xcqQ/ViProm_Eg-I/AAAAAAAADnY/aiAN0eXhQSQ/s1600-h/FileDestination%25255B9%25255D.jpg"><img title="FileDestination" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="FileDestination" src="http://lh3.googleusercontent.com/--K6iKu4ljRI/ViPrpycENDI/AAAAAAAADng/1KZCYg4x5vU/FileDestination_thumb%25255B7%25255D.jpg?imgmax=800" width="644" height="324"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-8LqIE56LBak/ViPrq00ZIFI/AAAAAAAADno/6yBOm6IKrlc/s1600-h/finalDesign%25255B5%25255D.jpg"><img title="finalDesign" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="finalDesign" src="http://lh3.googleusercontent.com/-2Rlb9PZQoac/ViPrrpEg3zI/AAAAAAAADnw/4bFmRTO4WCI/finalDesign_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="354"></a></p> <p>Step 4:- If you face any difficulty in <strong>Derived column configuration</strong> 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 <strong>Multicast providing</strong> similar row count to different output which is 295 rows.</p> <p><a href="http://lh3.googleusercontent.com/-JEZ7PzCKIdQ/ViPrszl47KI/AAAAAAAADn4/ge4Xo9gEy4g/s1600-h/Final_Result%25255B3%25255D.jpg"><img title="Final_Result" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Final_Result" src="http://lh3.googleusercontent.com/-5dQLZ8KjIpg/ViPruJTiSGI/AAAAAAAADoA/U4Z_W1UB6JQ/Final_Result_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="343"></a></p> <p>We , can use this <strong>Multicast</strong> where we <strong>need multiple copy of same source data</strong>.</p> <p>I hope this article might help you somewhere.</p> <p>Enjoy !!!</p> <p>RJ !!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-74355243051007429812015-10-18T05:41:00.000-07:002015-10-18T05:46:54.826-07:00Step by Step SSIS–Derived Column Transformation TIP # 120<p>Dear Friends,</p> <p>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.</p> <p>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. </p> <p>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.</p> <p>Isn’t it simple ?</p> <p>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)</p> <p><a href="http://lh3.googleusercontent.com/-vH-QTw7rSpg/ViOUeUXOoaI/AAAAAAAADjM/UHktB30JpKI/s1600-h/Data1%25255B6%25255D.jpg"><img title="Data1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Data1" src="http://lh3.googleusercontent.com/-l5cE3XtfRv8/ViOUfbLR9iI/AAAAAAAADjU/Pa42EO3WKE8/Data1_thumb%25255B4%25255D.jpg?imgmax=800" width="538" height="484"></a></p> <p>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. </p> <p>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</p> <p><a href="http://lh3.googleusercontent.com/-ShS-TTKVDdY/ViOUgpzRyJI/AAAAAAAADjc/ENZr_LSeAL4/s1600-h/SourceColumn%25255B8%25255D.jpg"><img title="SourceColumn" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="SourceColumn" src="http://lh3.googleusercontent.com/-G_q9uolpy_I/ViOUhqfysYI/AAAAAAAADjk/dhSCC2ZDWMk/SourceColumn_thumb%25255B6%25255D.jpg?imgmax=800" width="593" height="484"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-XnWVsIJB6jI/ViOUih880BI/AAAAAAAADjs/ATxX9ubquTI/s1600-h/DerivedColumnSettings%25255B12%25255D.jpg"><img title="DerivedColumnSettings" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="DerivedColumnSettings" src="http://lh3.googleusercontent.com/-FdfxgHRyIsw/ViOUlFvN5yI/AAAAAAAADj0/YGshoqUzm0I/DerivedColumnSettings_thumb%25255B10%25255D.jpg?imgmax=800" width="584" height="484"></a></p> <p>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.</p> <p>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</p> <p><a href="http://lh3.googleusercontent.com/-Wf2SwagY8J0/ViOUl4q9unI/AAAAAAAADj8/X3oNCA1CLrM/s1600-h/Expression%25255B9%25255D.jpg"><img title="Expression" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Expression" src="http://lh3.googleusercontent.com/-995MZpyFEh4/ViOUm7yoaLI/AAAAAAAADkE/BXa1AeaTG4Q/Expression_thumb%25255B7%25255D.jpg?imgmax=800" width="644" height="113"></a></p> <p>if you see below screenshot we can add other functions ,operators also.</p> <p><a href="http://lh3.googleusercontent.com/-Pa0B6EdgyZg/ViOUoMwXI6I/AAAAAAAADkM/BPoHBo3EXKc/s1600-h/VariousOpeartors%25255B7%25255D.jpg"><img title="VariousOpeartors" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="VariousOpeartors" src="http://lh3.googleusercontent.com/-DoF9Ztys2_E/ViOUo1dl9JI/AAAAAAAADkU/8w0_wuXsDro/VariousOpeartors_thumb%25255B5%25255D.jpg?imgmax=800" width="644" height="271"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-4FDbwY1pBk0/ViOUqfJKq2I/AAAAAAAADkc/mm52Vp72v6M/s1600-h/SellingRatemapping%25255B7%25255D.jpg"><img title="SellingRatemapping" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="SellingRatemapping" src="http://lh3.googleusercontent.com/-87OmjLG9SdQ/ViOUspyWfDI/AAAAAAAADkk/FgVhI9N79c0/SellingRatemapping_thumb%25255B5%25255D.jpg?imgmax=800" width="601" height="484"></a></p> <p>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</p> <p><a href="http://lh3.googleusercontent.com/-Qejrtol-exI/ViOUuKlq1gI/AAAAAAAADks/GkefCBInFo4/s1600-h/FinalResult%25255B7%25255D.jpg"><img title="FinalResult" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="FinalResult" src="http://lh3.googleusercontent.com/-sgudl2S-zx0/ViOUvCoa5AI/AAAAAAAADk0/BD49nKTizW0/FinalResult_thumb%25255B5%25255D.jpg?imgmax=800" width="509" height="484"></a></p> <p>I hope this post might help you to understand the derived column.</p> <p>Enjoy !!!</p> <p>RJ!!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-88769531256751307902015-10-17T05:24:00.000-07:002015-10-17T05:37:09.618-07:00Step by Step SSIS–Union ALL tip # 119<p> </p> <p>Dear All, </p> <p>In the series of zero to hero <strong>SSIS </strong>series this is one of the simplest transformation control which known as <strong>UNION ALL.</strong> </p> <p>As you might aware <strong>UNION</strong> in general term which means collective. In <strong>SSIS UNION</strong> all control is doing the same task. </p> <p>It collect all the inputs may be of same type of sources or different type of sources and union them all and provides single output for the same.</p> <p>Lets understand this by below example.</p> <p>Suppose, We have different text files which contain fruits & vegetables name. Now our aim is to combine all the fruits and vegetables name which exists in these files.</p> <p>if you see below image you will find 3 files which FruitA (Contains fruit names start with letter A) , FruitB (Contain fruit names start with letter B), FruitP (contains fruit name start with letter P)</p> <p><a href="http://lh3.googleusercontent.com/-C9bGDSiUEn0/ViI-UXCalWI/AAAAAAAADf8/wI4YbITglEc/s1600-h/Files%25255B9%25255D.jpg"><img title="Files" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="Files" src="http://lh3.googleusercontent.com/-TWe1AV0QAyY/ViJAqqtwESI/AAAAAAAADgI/AG1qH1_Rezo/Files_thumb%25255B7%25255D.jpg?imgmax=800" width="644" height="394"></a> Now we need to combine this file using <strong>UNION ALL transformation</strong>. So follow below step by step</p> <p>1) Step 1:- Step 1 is simplest step and now you all well versed in this. We need to add a new <strong>SSIS package</strong> file and drag drop <strong>data flow task</strong> .</p> <p>2) Step 2:- Now add different <strong>flat file source</strong> for all the 3 files and configure all the 3 files fruitA.txt, fruitB.txt , fruitP.txt .</p> <p>3) Once we configured all the 3 flat file sources, We have to drag drop <strong>UNION ALL transformation</strong> control.Once we drag drop <strong>UNION ALL</strong> we can provide the output of all the 3 files as a input in <strong>UNION ALL</strong> control as shown in below figure.</p> <p><a href="http://lh3.googleusercontent.com/-ixAtxpNGFoY/ViJAr2KIvoI/AAAAAAAADgQ/WnYwrKsuz78/s1600-h/source%25255B6%25255D.jpg"><img title="source" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="source" src="http://lh3.googleusercontent.com/-dek3D_hUSi8/ViJAsqlFq4I/AAAAAAAADgY/iANqKEFqwuM/source_thumb%25255B4%25255D.jpg?imgmax=800" width="644" height="323"></a></p> <p>Step 4:- Now, we have to configure the <strong>UNION ALL</strong> control. To do this right click on<strong> UNION ALL</strong> control and click on EDIT option. Here we have to configure all the columns which we need to union.</p> <p><a href="http://lh3.googleusercontent.com/-tFa6zz4YXqk/ViJAuA_B1cI/AAAAAAAADgg/B12af3oK8PY/s1600-h/ConfigureUnionAll%25255B8%25255D.jpg"><img title="ConfigureUnionAll" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="ConfigureUnionAll" src="http://lh3.googleusercontent.com/-Ali5IRqkwHY/ViJAxS53PYI/AAAAAAAADgo/r9bBT928CEo/ConfigureUnionAll_thumb%25255B6%25255D.jpg?imgmax=800" width="568" height="484"></a></p> <p>Step 5:- Once the <strong>UNION ALL</strong> is configured our next step is to get the output of <strong>UNION ALL</strong> in a resultant file. for this we have to drag drop destination flat file control as shown in below figure.</p> <p><a href="http://lh3.googleusercontent.com/-lE10T0O1B8o/ViJAzfqBaAI/AAAAAAAADgw/1wuSajEk7Gw/s1600-h/file%25255B12%25255D.jpg"><img title="file" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="file" src="http://lh3.googleusercontent.com/-THnKEYtEvts/ViJA0TuMNNI/AAAAAAAADg4/1oGst5b6Exw/file_thumb%25255B8%25255D.jpg?imgmax=800" width="644" height="253"></a></p> <p>Step 6:- Now we have to configure the flat file destination and save it to specific location. A part from this we have to provide input to<strong> flat file destination</strong> which will be output of <strong>UNION ALL</strong> control. As shown in below figure I am saving the file on same location and giving name finaloutput.txt</p> <p><a href="http://lh3.googleusercontent.com/-cB5zFQceGIw/ViJA19woONI/AAAAAAAADhA/CeXE1fNt9zc/s1600-h/finaloutput%25255B7%25255D.jpg"><img title="finaloutput" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="finaloutput" src="http://lh3.googleusercontent.com/-3WVYWWR9Np4/ViJA4aA0yJI/AAAAAAAADhI/YLiQaS9DWFQ/finaloutput_thumb%25255B5%25255D.jpg?imgmax=800" width="637" height="484"></a></p> <p>Step 7:- Once all the above step is processed by us the final step is to run the package for this just click on RUN icon or hit F5. If everything is working fine the we will get following result with all the green right check images.</p> <p><a href="http://lh3.googleusercontent.com/-5eOZPL4kfcM/ViJA6WCq-PI/AAAAAAAADhQ/56_4bSX3c9k/s1600-h/finaloutputREsult%25255B6%25255D.jpg"><img title="finaloutputREsult" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="finaloutputREsult" src="http://lh3.googleusercontent.com/-nmZrVQ_3oV0/ViJA8zwUN3I/AAAAAAAADhY/xru85iFG3bo/finaloutputREsult_thumb%25255B4%25255D.jpg?imgmax=800" width="644" height="429"></a></p> <p>If you see above image we did union of all the 3 files and saved output in a single file. </p> <p>Now, for learning purpose and to make the post simplest I use only same type source you can use different type of source and destination as per your need.</p> <p>A part from this <strong>UNION ALL</strong> <strong>doesn’t remove duplicate so if there are duplicates in the files it will</strong> not remove.</p> <p>I hope this article will help you somewhere. Please provide your inputs.</p> <p>Enjoy !!!</p> <p>RJ!!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-1825776029683512362015-10-02T02:27:00.000-07:002015-10-02T08:38:14.212-07:00Step by Step SSIS–Aggregate Transformation TIP #118<p> </p> <p>Dear Friends,</p> <p>In the series of <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">step by step SSIS</a> tutorial this is another post. In this post we will see <strong>Aggregate</strong> <strong>Transformation</strong>. I am pretty much sure you are aware of aggregation. Although , Just wanted to share that <strong>aggregation</strong> operation in generally <strong>memory expensive</strong> operation. </p> <p>So, whenever you want <strong>aggregation</strong> & want to use <strong>group by function</strong> then in such situation you can use <strong>Aggregation transformation</strong>.</p> <p>There are different group by option available like<strong> MIN, MAX , COUNT, SUM, AVERAGE etc.</strong></p> <p>Let’s understand how to use <strong>Aggregate transformation</strong> step by step.</p> <p>For current example we are using <strong>Adventureworks</strong> database and we are using below query. Here we are fetching the product data with<strong> line total</strong>,<strong>unit</strong> <strong>price</strong> & other details.</p> <p><a href="http://lh3.googleusercontent.com/-2rMIWLnZZf4/Vg6kXXzgt9I/AAAAAAAADZ8/OiiKG1u1Heo/s1600-h/Data%25255B13%25255D.jpg"><img title="Data" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Data" src="http://lh3.googleusercontent.com/-tE-URufJs3k/Vg6kZEclfeI/AAAAAAAADaE/gikzbT2bKag/Data_thumb%25255B11%25255D.jpg?imgmax=800" width="500" height="484"></a></p> <p>if you see the records in the table you will find that there are multiple records for<strong> same product with different line total</strong>.</p> <p>Our objective is to <strong>aggregate or do sum of Line total according</strong> to Line Number and export the result in a csv.</p> <p><strong>Step 1:-</strong> So , Now start with package creation add a new package in solution and <strong>drag drop source Assistant</strong> and <strong>configure</strong> the database connection as we did earlier in the tutorials</p> <p>Step 1.1 – <strong>Drag drop Data flow task</strong></p> <p><a href="http://lh3.googleusercontent.com/-9nIznNyClS8/Vg6kaJIJzKI/AAAAAAAADaM/G-vBbt-lwio/s1600-h/Step%2525201%25255B6%25255D.jpg"><img title="Step 1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step 1" src="http://lh3.googleusercontent.com/-HtpRUIxFh2U/Vg6kbSqIolI/AAAAAAAADaU/-zw7wy8rYfM/Step%2525201_thumb%25255B4%25255D.jpg?imgmax=800" width="644" height="294"></a></p> <p>Step 1.2 – double click <strong>data flow task</strong> and drag drop <strong>source assistance control</strong> </p> <p><a href="http://lh3.googleusercontent.com/-H6Aw58CAFaY/Vg6keGJxWdI/AAAAAAAADac/ffxvFNzEmE8/s1600-h/Step%2525201.1%25255B5%25255D.jpg"><img title="Step 1.1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step 1.1" src="http://lh3.googleusercontent.com/-3klZ9-kyJUY/Vg6kgKtNcVI/AAAAAAAADak/hHesrmTBByM/Step%2525201.1_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="324"></a></p> <p>Step 1.3 – <strong>Configure source assistance</strong> </p> <p><a href="http://lh3.googleusercontent.com/-bcvep6SRddI/Vg6khUo5OAI/AAAAAAAADas/tb2reAGn-BU/s1600-h/Step%2525201.2%25255B5%25255D.jpg"><img title="Step 1.2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step 1.2" src="http://lh3.googleusercontent.com/-FQT-Vy18xyM/Vg6kjaWNDlI/AAAAAAAADa0/hR9NvBdJ164/Step%2525201.2_thumb%25255B3%25255D.jpg?imgmax=800" width="609" height="484"></a></p> <p>Step 2: Once the source assistance is configured with SQL SERVER connection string and specific query we will drag drop <strong>Aggregate Transformation</strong> control as shown in below figure</p> <p><a href="http://lh3.googleusercontent.com/-I7KoFu9FvGY/Vg6kkcINfhI/AAAAAAAADa8/xEjDv2EGJlU/s1600-h/Step2%25255B5%25255D.jpg"><img title="Step2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step2" src="http://lh3.googleusercontent.com/-IfozdIcV9-U/Vg6kl4dD3FI/AAAAAAAADbE/YIqM0LXHg5Q/Step2_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="285"></a></p> <p>Step 3:- Now configure this <strong>aggregate control</strong>. So Just right click <strong>aggregate control</strong> and select Edit option. Now as we require Sum of LineTotal so we have selected SUM in operation column’s drop down and rest other has drop down option group by.</p> <p> </p> <p><a href="http://lh3.googleusercontent.com/-nTobvRrUtcQ/Vg6knNvsJaI/AAAAAAAADbM/lfdC3iz-whw/s1600-h/Step3%25255B9%25255D.jpg"><img title="Step3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step3" src="http://lh3.googleusercontent.com/-fuVP5hw4j1k/Vg6kosN1cXI/AAAAAAAADbU/wPtq2zt_2Fo/Step3_thumb%25255B7%25255D.jpg?imgmax=800" width="576" height="484"></a></p> <p>Step 4:- </p> <p>Now, drag drop flat file destination and configure it.</p> <p><a href="http://lh3.googleusercontent.com/-gWK4Ql49cW4/Vg6kqR2d6sI/AAAAAAAADbc/wV-rTN-G5sA/s1600-h/Step4%25255B9%25255D.jpg"><img title="Step4" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step4" src="http://lh3.googleusercontent.com/-_V555o215aI/Vg6kruebOkI/AAAAAAAADbk/ZKTiJ5XPUSM/Step4_thumb%25255B4%25255D.jpg?imgmax=800" width="644" height="403"></a></p> <p>Now <strong>configure</strong> <strong>flat file with</strong> mapping as shown in below figures </p> <p><a href="http://lh3.googleusercontent.com/-AyE7UhjWAuI/Vg6kt1nQrnI/AAAAAAAADbs/yAbjbRFUB7Y/s1600-h/Step%2525204.1%25255B5%25255D.jpg"><img title="Step 4.1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step 4.1" src="http://lh3.googleusercontent.com/-icn56LlpaLE/Vg6kvP3CWGI/AAAAAAAADb0/nq1P5rLGJws/Step%2525204.1_thumb%25255B3%25255D.jpg?imgmax=800" width="558" height="484"></a></p> <p><a href="http://lh3.googleusercontent.com/-jBAIIHyh7G0/Vg6kwHEfSNI/AAAAAAAADb8/C_JkqjxWAZQ/s1600-h/Step%2525204.2%25255B5%25255D.jpg"><img title="Step 4.2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step 4.2" src="http://lh3.googleusercontent.com/-94EvNYYyyHQ/Vg6kyA2RrtI/AAAAAAAADcE/1XpuRgJqZoM/Step%2525204.2_thumb%25255B3%25255D.jpg?imgmax=800" width="600" height="484"></a></p> <p>Step 5: Now run it you will get desire result as shown in below figure </p> <p><a href="http://lh3.googleusercontent.com/-a32MuuoWw_U/Vg6kzXib02I/AAAAAAAADcM/7qQFY0v9sEU/s1600-h/Result%25255B5%25255D.jpg"><img title="Result" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Result" src="http://lh3.googleusercontent.com/-tcCcuzAE66E/Vg6k0mIaQvI/AAAAAAAADcU/xxlhQyyX92M/Result_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="467"></a></p> <p>If you see in the result <strong>we got 757 rows after processing 121,317</strong></p> <p>Now see the result in actual as shown in below file </p> <p><a href="http://lh3.googleusercontent.com/-33IoR1a1mp8/Vg6k2kueO9I/AAAAAAAADcc/mO2O8kygcQo/s1600-h/File%25255B5%25255D.jpg"><img title="File" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="File" src="http://lh3.googleusercontent.com/-rvRhcpGjfyc/Vg6k5MwEg7I/AAAAAAAADck/J4D26N-d-5o/File_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="369"></a></p> <p>I hope this example might help you to understand <strong>Aggregate transformation</strong>. Please provide your inputs.</p> <p>Enjoy !!</p> <p>RJ!!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-86994071034495475342015-09-21T01:53:00.000-07:002015-09-21T04:46:37.953-07:00Step by Step SSIS - Conditional Split Transformation TIP #117<p>In the series of <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">Zero to Hero in SSIS</a> this is our next post. In this post we will see <strong>Conditional Split transformation</strong>.</p> <p>I am pretty much sure that by the name you got some impression what it would be.</p> <p>So, a <strong>Conditional Split Transformation</strong> is a way by which you can conditionally split an input into multiple output.</p> <p>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.</p> <p>Let’s understand it by following step by step execution example.</p> <p>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 <strong>Accessories</strong> , <strong>bikes</strong> are the categories.</p> <p><a href="http://lh3.googleusercontent.com/-ze2ZTgt8-WI/Vf_tyIyCczI/AAAAAAAADSs/OwH44fYp57M/s1600-h/Query1%25255B3%25255D.jpg"><img title="Query1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Query1" src="http://lh3.googleusercontent.com/-fm4s-RfzbPA/Vf_tzKSUEjI/AAAAAAAADS0/cWa30Dim43w/Query1_thumb%25255B1%25255D.jpg?imgmax=800" width="634" height="484"></a></p> <p>Step 1:- Create a new package and drag drop data flow task control and double click it. You will get a new screen which <strong>data flow task</strong>. Now drag drop <strong>Source Assistance</strong>. from SSIS controls tool bar.</p> <p><a href="http://lh3.googleusercontent.com/-Tp0x7aIjFWU/Vf_tzu0dGrI/AAAAAAAADS4/rmsR2QWHtrU/s1600-h/Step1%25255B3%25255D.jpg"><img title="Step1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step1" src="http://lh3.googleusercontent.com/-7MOgFEwRVQ4/Vf_t1MeqI3I/AAAAAAAADTE/fCRejUgBSE4/Step1_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="285"></a></p> <p>Now as usual we have to configure the database connection string and set the database to adventureworks.</p> <p><a href="http://lh3.googleusercontent.com/-EbLPPKzjdL0/Vf_t1idltdI/AAAAAAAADTM/28L9N1heM1Y/s1600-h/step2%25255B3%25255D.jpg"><img title="step2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step2" src="http://lh3.googleusercontent.com/-a8Vx0OvI614/Vf_t2fn-EdI/AAAAAAAADTQ/XBmuD6yOUks/step2_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="310"></a></p> <p>Step 2:- Once the database is configured right click on control and choose <strong>edit property</strong>. You will get below screen where you need to specify the query which we shown earlier in figure.</p> <p><a href="http://lh3.googleusercontent.com/-qPQMHzYm3oI/Vf_t3YJta-I/AAAAAAAADTc/f36HJXxkIeI/s1600-h/step3%25255B7%25255D.jpg"><img title="step3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step3" src="http://lh3.googleusercontent.com/-K6dgio-UN3A/Vf_t4OmZyGI/AAAAAAAADTg/CkPYmLeW6O4/step3_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="481"></a></p> <p>Once you done with above steps you need to configure columns as well so, click on Columns and configure it.</p> <p><a href="http://lh3.googleusercontent.com/-l9TzrJ48LLA/Vf_t4ufsiBI/AAAAAAAADTo/AbGI-h-PV2E/s1600-h/step4%25255B3%25255D.jpg"><img title="step4" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step4" src="http://lh3.googleusercontent.com/-Ok0GcqRxIdI/Vf_t5366qqI/AAAAAAAADT0/qz-LVd6uw8k/step4_thumb%25255B1%25255D.jpg?imgmax=800" width="639" height="484"></a></p> <p>Step 3:- Now drag drop conditional split and connect input arrow to it as shown in figure</p> <p><a href="http://lh3.googleusercontent.com/-AD8K_7DRsCs/Vf_t6ToLywI/AAAAAAAADT4/MUIB3ijWM1c/s1600-h/step5%25255B3%25255D.jpg"><img title="step5" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step5" src="http://lh3.googleusercontent.com/-XpMB3H-mlqg/Vf_t7MoizCI/AAAAAAAADUA/HjlIjniCCXg/step5_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="338"></a></p> <p>Step 4:- Now choose edit option by right clicking the <strong>conditional split and configure it</strong>. 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.</p> <p><a href="http://lh3.googleusercontent.com/-04SarKIeG1M/Vf_t8E_GEVI/AAAAAAAADUM/tk2L0--DVqE/s1600-h/step6%25255B7%25255D.jpg"><img title="step6" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step6" src="http://lh3.googleusercontent.com/-PU6z3RwIZmg/Vf_t8kcFimI/AAAAAAAADUQ/NyUWb_rZds4/step6_thumb%25255B3%25255D.jpg?imgmax=800" width="498" height="484"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/--Y-g3Kg9ijU/Vf_t9Y0xrXI/AAAAAAAADUY/JZSrM22k7Oc/s1600-h/Step7%25255B3%25255D.jpg"><img title="Step7" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step7" src="http://lh3.googleusercontent.com/-yc0h1rdPu24/Vf_t-d6MArI/AAAAAAAADUk/x99AoTBzbYw/Step7_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="374"></a></p> <p>Step 6: Now Configure the flat file destination by choosing the edit option.</p> <p><a href="http://lh3.googleusercontent.com/-GPC-Zcksp8I/Vf_t_Eu1GsI/AAAAAAAADUo/daEKR9Vyztw/s1600-h/step10%25255B3%25255D.jpg"><img title="step10" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step10" src="http://lh3.googleusercontent.com/-eKfaW5xAypI/Vf_t_pvtKFI/AAAAAAAADUw/-SsY0i7itbc/step10_thumb%25255B1%25255D.jpg?imgmax=800" width="570" height="484"></a></p> <p> </p> <p><a href="http://lh3.googleusercontent.com/-8XWebdF74us/Vf_uAe-YQOI/AAAAAAAADU4/FtvjWeEEuQc/s1600-h/Step9%25255B3%25255D.jpg"><img title="Step9" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step9" src="http://lh3.googleusercontent.com/-fIEfJ4gYg8k/Vf_uBLYiunI/AAAAAAAADVA/l3gMgTdKKiI/Step9_thumb%25255B1%25255D.jpg?imgmax=800" width="596" height="484"></a></p> <p>Step 7:- In similar way we have to capture the output which not belongs to Bikes category as shown in below figure</p> <p><a href="http://lh3.googleusercontent.com/-ZJc5LhKkMWM/Vf_uBoJGtwI/AAAAAAAADVM/EUSpDY7hkUU/s1600-h/step8%25255B4%25255D.jpg"><img title="step8" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step8" src="http://lh3.googleusercontent.com/-2Og7SVObU3A/Vf_uCjtkzFI/AAAAAAAADVU/UtfTrtdYk_U/step8_thumb%25255B2%25255D.jpg?imgmax=800" width="560" height="484"></a></p> <p>Step 8: In nutshell we will get following screen</p> <p><a href="http://lh3.googleusercontent.com/-QLgyQ7jdFKw/Vf_uDNqcHUI/AAAAAAAADVY/Wrj7AxciEPI/s1600-h/step11%25255B3%25255D.jpg"><img title="step11" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step11" src="http://lh3.googleusercontent.com/-8gLgao4NP90/Vf_uD9DkBxI/AAAAAAAADVg/MjvSwYx_ZiE/step11_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="477"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-98AlN1ogbjk/Vf_uEXlWYhI/AAAAAAAADVo/sMVrqqJ1_EU/s1600-h/Step12%25255B3%25255D.jpg"><img title="Step12" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step12" src="http://lh3.googleusercontent.com/-cHBxZ0HpqYs/Vf_uFKWriYI/AAAAAAAADVw/rjqJ9G-xT04/Step12_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="441"></a></p> <p>To cross check result we will see the file output as well</p> <p><a href="http://lh3.googleusercontent.com/-zLrAINBMdbM/Vf_uGNm7-oI/AAAAAAAADV8/nFCgTL-9OIU/s1600-h/BikeCategories%25255B3%25255D.jpg"><img title="BikeCategories" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="BikeCategories" src="http://lh3.googleusercontent.com/-TXzP_xnc8gY/Vf_uHMHbAUI/AAAAAAAADWA/no2Y3rqAla8/BikeCategories_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="294"></a></p> <p>So , If you go through all the above steps we have achieved <strong>Conditional split example</strong>. </p> <p>I hope this post might help you to understand <strong>conditional split.</strong></p> <p>In next step we will go for next step in SSIS. </p> <p>Enjoy !!!</p> <p>RJ!!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com0tag:blogger.com,1999:blog-6538321000057298497.post-67439350536713789562015-09-13T10:12:00.000-07:002015-09-13T20:43:30.758-07:00Step by Step SSIS learning What Data Conversion use in SSIS ? TIP#116<p>Dear Friends,</p> <p>In the series of <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">step by step learning of SSIS</a> this is part #6 in which we are going to learn a new control<strong> Data Conversion.</strong> 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<strong> CONVERT or CAST</strong> in our programming.</p> <p>Although, Here it would be important to share SSIS also have data type which are similar to other programming or database’s data types</p> <p>Like <strong>DT_NUMERIC</strong> is equivalent to numeric data type, <strong>DT_I2</strong> is equivalent to smallint, <strong>DT_str </strong>is equivalent to VARCHAR and so on.</p> <p>I am sure you will find this control fairly simple so without wasting much time lets start step by step. </p> <p>For a change I am using excel data source here.</p> <p>Suppose, we have an <strong>excel of sales order</strong> which have columns like <strong>sales order, subtotal,Tax amount, and freight</strong>. </p> <p><a href="http://lh3.googleusercontent.com/-_YFH6kyixxI/VfZBuOx3ZOI/AAAAAAAADMM/rIXxX9zWiHM/s1600-h/SaleOrderExcel4.jpg"><img title="SaleOrderExcel" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="SaleOrderExcel" src="http://lh3.googleusercontent.com/-JXU_GeHT_-U/VfZBvkGqoHI/AAAAAAAADMU/lNw5c36Kyis/SaleOrderExcel_thumb2.jpg?imgmax=800" width="644" height="460"></a></p> <p>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.</p> <p>Step1 :- Drag drop a <strong>Data flow Task control</strong> and double click it. Now add an excel data source from SSIS tool bar as shown in below figure.</p> <p><a href="http://lh3.googleusercontent.com/-HqjENyaEfZc/VfZBwdb1nII/AAAAAAAADMc/eWal1Dmlj6s/s1600-h/ExcelSource31.jpg"><img title="ExcelSource" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ExcelSource" src="http://lh3.googleusercontent.com/-7nLLOwoyPso/VfZByVCixCI/AAAAAAAADMk/_BtsJ03Ka-o/ExcelSource_thumb1.jpg?imgmax=800" width="595" height="484"></a></p> <p>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. </p> <p><a href="http://lh3.googleusercontent.com/-qpawbkFV5O0/VfZB1xT6W8I/AAAAAAAADMs/N-Z9ru-bASs/s1600-h/ExcelSourceStep27.jpg"><img title="ExcelSourceStep2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ExcelSourceStep2" src="http://lh3.googleusercontent.com/-8WIwLmrT1AI/VfZCEgkRTwI/AAAAAAAADM0/RSL6ATc3uvM/ExcelSourceStep2_thumb2.jpg?imgmax=800" width="633" height="484"></a></p> <p>Once the connection with excel is established the <strong><em>Next very important step is to configure sheet as highlighted in below figure.</em></strong> 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.</p> <p><a href="http://lh3.googleusercontent.com/-t5W5CyI2PRM/VfZCFVcL-6I/AAAAAAAADM8/hEDiaMLtUeY/s1600-h/ExcelSource33.jpg"><img title="ExcelSource3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ExcelSource3" src="http://lh3.googleusercontent.com/-spT0crye60w/VfZCGPS3z8I/AAAAAAAADNE/W_rFnqn4pTE/ExcelSource3_thumb1.jpg?imgmax=800" width="643" height="484"></a></p> <p>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</p> <p><a href="http://lh3.googleusercontent.com/-iNh6FeIvXRI/VfZCHO9xEYI/AAAAAAAADNM/e14IrPe7hDs/s1600-h/ExcelSourceColumn3.jpg"><img title="ExcelSourceColumn" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ExcelSourceColumn" src="http://lh3.googleusercontent.com/-k3gOHIOu8yg/VfZCIB0shsI/AAAAAAAADNU/bV_27bZ58b4/ExcelSourceColumn_thumb1.jpg?imgmax=800" width="606" height="484"></a></p> <p>Step 4:- Now drag drop the data conversion control from SSIS toolbar </p> <p><a href="http://lh3.googleusercontent.com/-j4lkxszYpyk/VfZCI8nvTaI/AAAAAAAADNc/gJfQoIwRmHw/s1600-h/dataConvrsionControl3.jpg"><img title="dataConvrsionControl" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="dataConvrsionControl" src="http://lh3.googleusercontent.com/-HEnU389xcvA/VfZCKUKt2yI/AAAAAAAADNk/MpAjq8zayfs/dataConvrsionControl_thumb1.jpg?imgmax=800" width="644" height="268"></a></p> <p>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 <strong>integer (DT_I4) </strong>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 <strong>copy of as a prefix on column.</strong></p> <p><a href="http://lh3.googleusercontent.com/-0E8JoU3ciBk/VfZCLsaKg8I/AAAAAAAADNs/bdilCgpw0o0/s1600-h/Convesion7.jpg"><img title="Convesion" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Convesion" src="http://lh3.googleusercontent.com/-d0atZw2HR2E/VfZCM-UdHxI/AAAAAAAADN0/giTpRx3OCn4/Convesion_thumb3.jpg?imgmax=800" width="558" height="484"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-N5h9X19oeqM/VfZCN6fBLhI/AAAAAAAADN8/auHVUM2YP3k/s1600-h/FlatFile3.jpg"><img title="FlatFile" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="FlatFile" src="http://lh3.googleusercontent.com/-JTBEDCG9gts/VfZCOzpQR3I/AAAAAAAADOE/Hb9O2DbszU4/FlatFile_thumb1.jpg?imgmax=800" width="644" height="362"></a></p> <p>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.</p> <p><a href="http://lh3.googleusercontent.com/-fSj0Cn1CP5Y/VfZCPkNyj3I/AAAAAAAADOM/fmAkM31JUB0/s1600-h/deleteAccesscolumns3.jpg"><img title="deleteAccesscolumns" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="deleteAccesscolumns" src="http://lh3.googleusercontent.com/-HA-3BlKUEOg/VfZCQiGeveI/AAAAAAAADOU/_xYF5Cro-r8/deleteAccesscolumns_thumb1.jpg?imgmax=800" width="536" height="484"></a></p> <p><a href="http://lh3.googleusercontent.com/-SEdMrf7fY0E/VfZCRhPyXOI/AAAAAAAADOc/tVQya0OgSNI/s1600-h/dataConvesionmapping3.jpg"><img title="dataConvesionmapping" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="dataConvesionmapping" src="http://lh3.googleusercontent.com/-SFUJLiPZOW8/VfZCS5J5ToI/AAAAAAAADOk/Nj1sPNS0vAQ/dataConvesionmapping_thumb1.jpg?imgmax=800" width="601" height="484"></a></p> <p>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 <img class="wlEmoticon wlEmoticon-smile" style="border-top-style: none; border-bottom-style: none; border-right-style: none; border-left-style: none" alt="Smile" src="http://lh3.googleusercontent.com/-oZZYNlBnwZA/VfZCTSIIdXI/AAAAAAAADOs/PLBnSxA6sfU/wlEmoticon-smile2.png?imgmax=800"> .</p> <p>You will get find below screen. Which shows that all the provided rows are inserted in file. </p> <p><a href="http://lh3.googleusercontent.com/-Z8ZwxvtZSuo/VfZCUc89K-I/AAAAAAAADO0/PZUoJapMwTk/s1600-h/result3.jpg"><img title="result" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="result" src="http://lh3.googleusercontent.com/-HGE13z2OQ-g/VfZCWbUSrbI/AAAAAAAADO8/2bk_tPBSH1g/result_thumb1.jpg?imgmax=800" width="457" height="484"></a></p> <p>Step 9 : To cross check whether file has integer columns or not lets open it.</p> <p><a href="http://lh3.googleusercontent.com/-ocZBpl1KdYE/VfZCXb7oPfI/AAAAAAAADPE/lzUODYIMB6U/s1600-h/CSVREsult3.jpg"><img title="CSVREsult" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="CSVREsult" src="http://lh3.googleusercontent.com/-iIHwZ_GAKw8/VfZCYX3hc1I/AAAAAAAADPM/bf_lw1S-VGg/CSVREsult_thumb1.jpg?imgmax=800" width="351" height="484"></a></p> <p>So, if you see above screen we did it successfully. So we achieved our goal in this post and used <strong>DATA conversion</strong> control successfully.</p> <p>I hope you feel this post useful.</p> <p>Thanks for reading this post. </p> <p>Enjoy !!!</p> <p>RJ </p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-33428026067539553252015-09-12T11:29:00.000-07:002015-09-12T11:46:17.166-07:00How to use SORT control in SSIS ? TIP #115<p>Dear Friends,</p> <p>In the <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">series of</a> <a href="http://sqlraaga.blogspot.in/2015/09/how-to-deploy-ssis-package-step-by-step.html" target="_blank">Learn SSIS step by step</a> this is the 5th post. Now from this post we are going to use each transformation control one by one.</p> <p>So, lets start with simplest one transformation control which is<strong> “Sort”. </strong></p> <p>By the name it is clear data it will <strong>sort</strong> the data which is provided to it and give sorted output as a result. Lets understand this by below step by step example. (Here I am not writing step to start visual studio and create a new project, I am pretty much sure you are aware of this now.)</p> <p>Before going further let me tell you want we are going to do here. We will have a input result (which will be a text file) and then sort it and save in another file. </p> <p>Step 1:-I renamed package to SortPackage if you want you can rename your package as well. Now <strong>drag drop a Data Flow task</strong> as shown below and double click DATA flow task</p> <p><a href="http://lh3.googleusercontent.com/-7l_SqacUCvU/VfRypLJh-VI/AAAAAAAADHk/WW3QN3-VfwU/s1600-h/Step1%25255B3%25255D.jpg"><img title="Step1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step1" src="http://lh3.googleusercontent.com/-F-lkHD6Oc6M/VfRyqc-CMyI/AAAAAAAADHs/xpMauddA9lI/Step1_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="432"></a></p> <p>Step 2:- When you click it you will get a data flow screen where you can <strong>drag drop FLAT file source</strong>. You can choose <strong>source assistance</strong> as well. Now configure this flat file source. Means give the path of the file which you want to have as a input source. I am taking a text file which contain fruits name in different order. as shown in below figure</p> <p><a href="http://lh3.googleusercontent.com/-Q_k2vUxV76k/VfRyrHINLtI/AAAAAAAADH0/57sXTcRVqpg/s1600-h/Step3%25255B3%25255D.jpg"><img title="Step3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step3" src="http://lh3.googleusercontent.com/-uYOiD59zMxE/VfRysJ7YcdI/AAAAAAAADH8/ktAHHrII7zo/Step3_thumb%25255B1%25255D.jpg?imgmax=800" width="431" height="484"></a></p> <p>Right click flat file source click on EDIT option and follow the screens</p> <p><a href="http://lh3.googleusercontent.com/-J32bonGfllM/VfRyuZ_bKkI/AAAAAAAADIE/wbmw8Rddpzg/s1600-h/Step4%25255B3%25255D.jpg"><img title="Step4" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step4" src="http://lh3.googleusercontent.com/-hdZ27_GdFBM/VfRyvjNf25I/AAAAAAAADIM/wcwQ8JwxxGE/Step4_thumb%25255B1%25255D.jpg?imgmax=800" width="517" height="484"></a></p> <p>Step 3:- Now once the <strong>file is configure</strong>. We have to <strong>drag drop SORT control from SSIS tool box</strong> as shown in below figure. </p> <p><a href="http://lh3.googleusercontent.com/-mBWMX6GCjII/VfRyw_58LAI/AAAAAAAADIU/aePMm-Sjilw/s1600-h/Step5%25255B3%25255D.jpg"><img title="Step5" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step5" src="http://lh3.googleusercontent.com/-r22r0G9mbjg/VfRyx_k09DI/AAAAAAAADIc/QfxCdqM9HVk/Step5_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="345"></a></p> <p>I also added the output of flat file source to sort control. As shown in above figure. Now configure it. Click on Sort control and you will get following screen. As show in below screen you can sort the data on any column and in any direction like Ascending or descending.</p> <p>Right there is only one input column which is name so we are sorting name in Ascending order as shown below.</p> <p><a href="http://lh3.googleusercontent.com/-RnE0hlfEL2g/VfRyy6vzcII/AAAAAAAADIk/X7Y4Fqi9lJs/s1600-h/Step6%25255B3%25255D.jpg"><img title="Step6" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step6" src="http://lh3.googleusercontent.com/-yBGos6jcvOE/VfRyz83oz7I/AAAAAAAADIs/cUPbzoVt2Hc/Step6_thumb%25255B1%25255D.jpg?imgmax=800" width="530" height="484"></a></p> <p>Step 4:- Now once this configuration is done we will save the data in new file with new name which will be sortedFruits. Now to achieve this <strong>drag drop the destination control. So I took same float file destination as shown in below figure</strong></p> <p><a href="http://lh3.googleusercontent.com/-zqRl5qoDovI/VfRy06tgp_I/AAAAAAAADI0/nwxInQ-lv5U/s1600-h/Step7%25255B3%25255D.jpg"><img title="Step7" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step7" src="http://lh3.googleusercontent.com/-GKj5weaXR6U/VfRy1vsNEhI/AAAAAAAADI8/KKy2xK20VGA/Step7_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="242"></a></p> <p>Step 5:- Now configure this file destination. Which means where we need to save this file and what are the columns which we required. so In current case there is only one column which is Name (Fruit name) and I am saving this file at same place with sortFruits name. so lets configure it by clicking right click on flat file destination and click on edit button</p> <p><a href="http://lh3.googleusercontent.com/-vsfArq3OTKs/VfRy28-a6hI/AAAAAAAADJE/DgVdKHLE9rQ/s1600-h/step8%25255B3%25255D.jpg"><img title="step8" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="step8" src="http://lh3.googleusercontent.com/-2yADL2NcT0g/VfRy4nkJVtI/AAAAAAAADJM/DWSsoXgiV_o/step8_thumb%25255B1%25255D.jpg?imgmax=800" width="614" height="484"></a></p> <p>Step 6:- Once this is configured we need to run this package by pressing F5.When you run hit F5 and everything going right then in execution screen at each step you will find green right check image as shown in below screen</p> <p><a href="http://lh3.googleusercontent.com/-3cyb-_y1l_w/VfRy5q_NFAI/AAAAAAAADJU/QW6bT-PsIc4/s1600-h/Step1.JPG9%25255B3%25255D.jpg"><img title="Step1.JPG9" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Step1.JPG9" src="http://lh3.googleusercontent.com/-5Hmryu22Ne4/VfRy7l_aLpI/AAAAAAAADJc/zOs8U16Leds/Step1.JPG9_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="482"></a></p> <p>Step 7: Now to cross check we will first see whether that file is created or not. If created then whether we have sorted data or not. So lets open the flat files.</p> <p><a href="http://lh3.googleusercontent.com/-u8J9CGqWJs0/VfRy8hJ6A4I/AAAAAAAADJk/rzKWmoX3mF4/s1600-h/result%25255B3%25255D.jpg"><img title="result" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="result" src="http://lh3.googleusercontent.com/-g7v7zUJgJJQ/VfRy91tMKXI/AAAAAAAADJs/8NYzfU38Enk/result_thumb%25255B1%25255D.jpg?imgmax=800" width="588" height="484"></a></p> <p>So, if you see at provided destination location a file is created and the data inside this file is sorted file.</p> <p>I hope you like this first transformation control. till then Enjoy!!!</p> <p>Thanks !!</p> <p>RJ!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com2tag:blogger.com,1999:blog-6538321000057298497.post-3623211364472279342015-09-05T00:55:00.000-07:002015-09-05T02:45:35.843-07:00How to deploy SSIS Package ? Step by Step SSIS TIP #114<p>Dear Friends, <br>In last <a href="http://sqlraaga.blogspot.in/2015/08/how-to-create-first-basic-package-with.html" target="_blank">post</a> we have successfully created our fist basic package. So, Now the next thing is how to deploy this package.</p> <p>We have created this package for someone else or our client so we need to run this package on his /her machine.</p> <p>For this we need to know <strong>how to deploy</strong>. Lets start this step by step </p> <p>Step 1:- Open the existing solution and right click on the solution you will get following options as shown in figure. You need to click on <strong>“Convert to</strong> <strong>Package Deployment Model” </strong> as highlighted below</p> <p><a href="http://lh3.googleusercontent.com/-jfa1QXEefaI/Veq3tdGntTI/AAAAAAAAC_Q/GfvfcFL4rlw/s1600-h/convert_To_Package%25255B3%25255D.jpg"><img title="convert_To_Package" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="convert_To_Package" src="http://lh3.googleusercontent.com/-qCqCK3_ebZ0/Veq3x1Le7NI/AAAAAAAAC_Y/n6EaFEy0olo/convert_To_Package_thumb%25255B1%25255D.jpg?imgmax=800" width="592" height="484"></a></p> <p>Step 2:- Once you click it you might get popup for confirmation just click OK.</p> <p><a href="http://lh3.googleusercontent.com/-ScZv91b9zOY/Veq30HuPuHI/AAAAAAAAC_g/mCygRLrQb3M/s1600-h/Package_model_cofirmation%25255B3%25255D.jpg"><img title="Package_model_cofirmation" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Package_model_cofirmation" src="http://lh3.googleusercontent.com/-9pJ3JR6mKLo/Veq34GjH2RI/AAAAAAAAC_o/eHWj5Bayb7I/Package_model_cofirmation_thumb%25255B1%25255D.jpg?imgmax=800" width="536" height="484"></a></p> <p>Step 3: Now click on Properties you will get following screen in which you need to select the <strong>deployment option.</strong> And need to select <strong>true value </strong>for <strong>CreateDeploymentUtility</strong> as highlighted below in figure. Once You make this option true press OK or Apply Button.</p> <p><a href="http://lh3.googleusercontent.com/-_PjeOmk-Ljg/Veq378kDS3I/AAAAAAAAC_w/1usLw95lfLc/s1600-h/Properties%25255B3%25255D.jpg"><img title="Properties" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Properties" src="http://lh3.googleusercontent.com/-CLZSqly07nE/Veq3_0RR9WI/AAAAAAAAC_4/BFDUzx_O9h4/Properties_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="375"></a> </p> <p>Step 4:- Once you done with this again right click on the solution and again click on <strong>Covert to Package deployment model.</strong> Now this time you will get Wizard screen as shown below. Click on Next button </p> <p><a href="http://lh3.googleusercontent.com/-VLXuOuPaNAU/Veq4IjsSkNI/AAAAAAAADAA/aAmIu28SuTA/s1600-h/Convert_Wizard_1%25255B3%25255D.jpg"><img title="Convert_Wizard_1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Convert_Wizard_1" src="http://lh3.googleusercontent.com/-OVs47hXtKoo/Veq4L95fgeI/AAAAAAAADAI/ehjSA3gT5T4/Convert_Wizard_1_thumb%25255B1%25255D.jpg?imgmax=800" width="517" height="484"></a><br></p> <p>Step 5:- You will get below screen which is package selection screen. (Suppose you have multiple packages in a solution so for which package you want to create deployment you need to check / Uncheck according to your need.) Once you selected the Package click on Next button.</p> <p><a href="http://lh3.googleusercontent.com/-cjBP8LatapQ/Veq4P2TT3lI/AAAAAAAADAQ/j_GDRNKZTU0/s1600-h/Wizard2%25255B3%25255D.jpg"><img title="Wizard2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Wizard2" src="http://lh3.googleusercontent.com/-UDI_AyC11Y4/Veq4R4yjisI/AAAAAAAADAY/mJpsNuxz4VI/Wizard2_thumb%25255B1%25255D.jpg?imgmax=800" width="518" height="484"></a></p> <p>Step 6:- When you click next button you will get next option which is specific project properties as shown below in screen. In this we have an important option which is <strong>Protection level</strong>. It is use to set the level of showing sensitive data in package. for example a package may contain sql server connection string in which we have username and password and username and password are very important and sensitive information. Here ,with protection level we have option to encrypt this sensitive data or make package password protected. This will be an interesting topic which we will discuss separately in near future in separate post. for current time being just leave the default option as is and click on next button. </p> <p><a href="http://lh3.googleusercontent.com/-1FBRdO6kKn8/Veq4VPtPvwI/AAAAAAAADAg/dcJHVeFsB48/s1600-h/wizard3%25255B3%25255D.jpg"><img title="wizard3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="wizard3" src="http://lh3.googleusercontent.com/-IyKsOKMzWN8/Veq4WPI8YtI/AAAAAAAADAo/_fFekC2GmvA/wizard3_thumb%25255B1%25255D.jpg?imgmax=800" width="528" height="484"></a></p> <p>Step 7:- When you click on next button you will get following screen. This is the screen where you can call another package which this deployment. We will discuss it later for current deployment process we don’t want to call any other package. Just click on next button.</p> <p><a href="http://lh3.googleusercontent.com/-ol33jfqLG4c/Veq4X5nnW5I/AAAAAAAADAw/PisFViY-R7E/s1600-h/wizard4%25255B3%25255D.jpg"><img title="wizard4" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="wizard4" src="http://lh3.googleusercontent.com/-XF5sUx3nxtI/Veq4dxmxX3I/AAAAAAAADA4/JY1uUixkQp4/wizard4_thumb%25255B1%25255D.jpg?imgmax=800" width="520" height="484"></a></p> <p>Step 8:- When you click on next button you will get following screen which says about configurations. We can create a configuration file which is just a simple XML file. If you are a .NET developer just assume it web.config file where we can keep connection string or other configurable item.</p> <p><a href="http://lh3.googleusercontent.com/-Lr4ubWR7dOo/Veq4iyxAZTI/AAAAAAAADBA/UMyIEQU9_Ww/s1600-h/wizard5%25255B3%25255D.jpg"><img title="wizard5" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="wizard5" src="http://lh3.googleusercontent.com/-iMFllo395AI/Veq4nAKxaAI/AAAAAAAADBI/bQrdMDERsYw/wizard5_thumb%25255B1%25255D.jpg?imgmax=800" width="521" height="484"></a></p> <p>Step 9:- We can create parameters which can be use to pass the values in packages. In current solution we have not configured even parameters so don’t think to much about this currently. We will discuss it separately in coming post.</p> <p><a href="http://lh3.googleusercontent.com/-MrBM3Dok15w/Veq4qFd9I9I/AAAAAAAADBQ/5G5JAWafH9k/s1600-h/wizard6%25255B3%25255D.jpg"><img title="wizard6" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="wizard6" src="http://lh3.googleusercontent.com/-Mk2XWP9hubI/Veq4seaIH5I/AAAAAAAADBY/h-65rSUojRc/wizard6_thumb%25255B1%25255D.jpg?imgmax=800" width="520" height="484"></a></p> <p>Step 10:- Now directly jump to Perform Conversion and skip review step. and click next you will get following screen of popup. Press OK and then try to rebuild the solution.</p> <p><a href="http://lh3.googleusercontent.com/-8PsB8USpP6g/Veq40wJkr0I/AAAAAAAADBg/k5sSYP8jl3E/s1600-h/wizard10%25255B3%25255D.jpg"><img title="wizard10" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="wizard10" src="http://lh3.googleusercontent.com/-Bv2Z60pVq7Y/Veq44mmdN5I/AAAAAAAADBo/Bt3J0EQ0x8M/wizard10_thumb%25255B1%25255D.jpg?imgmax=800" width="519" height="484"></a></p> <p>Step 11:- We did a build/rebuild to cross check package is correct. Now open the deployment folder in bin folder (You can find the address in step 3’s screen) . When you open the folder you will get two files one is Your package file with DTSX extension and another one which is called manifest file as shown in the figure below. Below ExportSQLToFlatFile is our <strong>manifest file and Package is our actual DTSX package file. (if we have config file then you will find config file as well)</strong></p> <p><a href="http://lh3.googleusercontent.com/-kZSs-Uv8u0c/Veq47cK82jI/AAAAAAAADBw/MRqN58SfFVI/s1600-h/Package_Manifest_Location%25255B3%25255D.jpg"><img title="Package_Manifest_Location" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Package_Manifest_Location" src="http://lh3.googleusercontent.com/-H6nTbiRNQy0/Veq4-6F7H-I/AAAAAAAADB4/pQJVyEuE_8A/Package_Manifest_Location_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="152"></a></p> <p>Step 12:- Now, we successfully completed our first part of deployment which is basically creating a package deployment file which is a manifest file. We can copy these files on any server where we need to deploy the package. When you click on Manifest file which is ExportSQLToFlatFile you will get following screen</p> <p><a href="http://lh3.googleusercontent.com/-Q2ZcKKQ-jus/Veq5HAOtDwI/AAAAAAAADCA/htISwJqdiIk/s1600-h/Actual_Deployment_1%25255B3%25255D.jpg"><img title="Actual_Deployment_1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Actual_Deployment_1" src="http://lh3.googleusercontent.com/-6SEerV5srbs/Veq5KPxx1kI/AAAAAAAADCI/PlNokqkiEY0/Actual_Deployment_1_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="399"></a></p> <p>Step 13: Click on Next button when you get above screen you will get following screen which shows 2 options of package deployment </p> <p>1) File System deployment 2) SQL Server deployment</p> <p>Now for current demo we are using file system deployment and clicking next button.</p> <p><a href="http://lh3.googleusercontent.com/-Z8b4JWPsEQA/Veq5OMkf0vI/AAAAAAAADCQ/j8YYH_in7jE/s1600-h/Actual_Deployment_2%25255B3%25255D.jpg"><img title="Actual_Deployment_2" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Actual_Deployment_2" src="http://lh3.googleusercontent.com/-6NMMD2BlCco/Veq5Y4JdQZI/AAAAAAAADCY/kX91gg7zYLI/Actual_Deployment_2_thumb%25255B1%25255D.jpg?imgmax=800" width="526" height="484"></a></p> <p>Step 14:- When we click next button we get the screen which ask for folder where package will be deploy by default it is SQL SERVER’s DTS package folder so let it be currently and click Next button </p> <p><a href="http://lh3.googleusercontent.com/-HrKAp6_GSTY/Veq5fAJTd6I/AAAAAAAADCg/HoU7O0v8Jxc/s1600-h/Actual_Deployment_3%25255B3%25255D.jpg"><img title="Actual_Deployment_3" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Actual_Deployment_3" src="http://lh3.googleusercontent.com/-kx3P9T2syPI/Veq5hXPVR2I/AAAAAAAADCo/XZ6OkEFKELE/Actual_Deployment_3_thumb%25255B1%25255D.jpg?imgmax=800" width="526" height="484"></a></p> <p>Step 15:- After clicking next button , we will get following screen. Which is a confirmation screen for deployment.</p> <p><a href="http://lh3.googleusercontent.com/-1NTEuIHGI9o/Veq5i2-XUQI/AAAAAAAADCw/c5L5TqltEiw/s1600-h/Actual_Deployment_4%25255B3%25255D.jpg"><img title="Actual_Deployment_4" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Actual_Deployment_4" src="http://lh3.googleusercontent.com/-Hl5hAF_GsF8/Veq5lUFbItI/AAAAAAAADC4/jp04vP--bwI/Actual_Deployment_4_thumb%25255B1%25255D.jpg?imgmax=800" width="529" height="484"></a></p> <p>Step 16:- Now the last step click the finish button and we are good to go.</p> <p><a href="http://lh3.googleusercontent.com/-rwlEqqq7o7A/Veq5qCs-25I/AAAAAAAADDA/t1BcWgM1u5Y/s1600-h/Actual_Deployment_5%25255B3%25255D.jpg"><img title="Actual_Deployment_5" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Actual_Deployment_5" src="http://lh3.googleusercontent.com/--wxvXXLu0HU/Veq5ucBhDxI/AAAAAAAADDI/n2SQiZuAWg0/Actual_Deployment_5_thumb%25255B1%25255D.jpg?imgmax=800" width="524" height="484"></a></p> <p>So all the above steps are for creating a deployment for package and then deploy in SQL SERVER. Now , I am sure your next question will be what now ?</p> <p>So, now we call this Package in SQL SERVER Job and configure job which execute this package time to time.</p> <p>We, will discuss it more in detail coming post.</p> <p>I hope you will like this post. please do post your inputs.</p> <p>Thanks</p> <p>Raj </p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-50634850915676237392015-08-29T02:58:00.000-07:002015-08-29T03:06:37.673-07:00How to create first basic package with SSIS ? tip #113<p>Dear friends,</p> <p>In last <a href="http://sqlraaga.blogspot.in/2015/08/wwh-of-ssis-zero-to-hero-in-ssis-series.html" target="_blank">post #112</a> we understood <strong>WWH (What ,Why & How ) of SSIS. Now</strong> , lets move now real quick in practical session where we will try to create a basic simple package.</p> <p>The example which we are creating is well known <strong>Export data from SQL SERVER to a flat file.</strong></p> <p><strong>Step 1:-</strong> Open <strong>SQL SERVER Data Tool from</strong> start menu</p> <p><a href="http://lh3.googleusercontent.com/--MSLzqC90Sw/VeGDuG1xAcI/AAAAAAAAC2w/PP47eETXm9g/s1600-h/Sql_server_data_tool3.jpg"><img title="Sql_server_data_tool" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Sql_server_data_tool" src="http://lh3.googleusercontent.com/-4-EEbI_AWYM/VeGDu7jckII/AAAAAAAAC24/ooNbaUOQo9I/Sql_server_data_tool_thumb1.jpg?imgmax=800" width="329" height="484"></a></p> <p>Step 2:- Once it is open create a new project by clicking new project option. You have to select proper template as highlighted in below figure and give a name to project. As shown in below figure</p> <p><a href="http://lh3.googleusercontent.com/-3yxSTSg3USo/VeGDv01l_HI/AAAAAAAAC3A/uUOIqhnBNJY/s1600-h/SSISCreate_Project_Indiandotnet3.jpg"><img title="SSISCreate_Project_Indiandotnet" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="SSISCreate_Project_Indiandotnet" src="http://lh3.googleusercontent.com/-4A8bNSYxDZE/VeGDwz81niI/AAAAAAAAC3I/PBCHCMWjt_0/SSISCreate_Project_Indiandotnet_thum.jpg?imgmax=800" width="644" height="397"></a></p> <p>Step 3:- Now drag drop <strong>data flow task control</strong> from <strong>SSIS toolbox. You can give customize message by click control’s </strong>text. I prefer this habit so down the line if after few month or years if you need to do some maintenance or logic change you don’t need to think a lot for why this control is for. </p> <p><a href="http://lh3.googleusercontent.com/-UmcROsh4G0s/VeGDxpNwB8I/AAAAAAAAC3Q/YaeJ6iT2bWc/s1600-h/DataFlowControl_indiandotnet_13.jpg"><img title="DataFlowControl_indiandotnet_1" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="DataFlowControl_indiandotnet_1" src="http://lh3.googleusercontent.com/-rx3Lovx8avg/VeGDyXMv4WI/AAAAAAAAC3Y/rhHHXEcphik/DataFlowControl_indiandotnet_1_thumb.jpg?imgmax=800" width="644" height="156"></a></p> <p><strong>Step 4:- </strong>Now double click on <strong>Data flow control</strong> or click on data flow tab.<strong> </strong>Now on this area you have to drag drop <strong>source assistance. When you drag drop it you will get a pop as shown below.</strong></p> <p><a href="http://lh3.googleusercontent.com/-RPRMEtM7D3A/VeGDzd8kOWI/AAAAAAAAC3g/HY0nxRDB1Vw/s1600-h/Source_Assistance%25255B3%25255D.jpg"><img title="Source_Assistance" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="Source_Assistance" src="http://lh3.googleusercontent.com/-2xSlSi-SBO8/VeGD0Xa5J3I/AAAAAAAAC3o/6P2xJmLJ328/Source_Assistance_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="322"></a></p> <p><strong></strong> </p> <p>The screen source assistance is the way by which we can select the data source on which we need to perform operation. As you are seeing in the image there are different data sources</p> <p>Like SQL SERVER, Excel, Flat file, Oracle.</p> <p>Although, you can select other sources also from SSIS toolbox as shown in below screen (As you are seeing there are various individual sources exists in toolbox itself so either use source assistance or drag drop individual source.It is worthless to explain here that excel source for excel file, flat file source for flat file and so on.</p> <p><a href="http://lh3.googleusercontent.com/-wTcZzaoU49Y/VeGD1HSmVFI/AAAAAAAAC3w/whf9FAjOUUg/s1600-h/OtherSource%25255B9%25255D.jpg"><img title="OtherSource" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="OtherSource" src="http://lh3.googleusercontent.com/-jOqiqijzxNk/VeGD16-jAfI/AAAAAAAAC34/pXSsfFCsjf8/OtherSource_thumb%25255B2%25255D.jpg?imgmax=800" width="187" height="234"></a></p> <p>In this example we are <strong>selecting SQL SERVER</strong>. When you select Source Type then you have to configure connection Manager.For this we have to select “NEW” in connection manager panel and click OK button.</p> <p>You will get below screen where you can give all the information related to SQL SERVER by which our package can connect with that data source. below I am using my SQL SERVER installed on my machine and using AdventureWorks database as shown in below image.</p> <p><a href="http://lh3.googleusercontent.com/-T7RmK12SXvI/VeGD26RWdII/AAAAAAAAC4A/oS1FO_DZflw/s1600-h/SQL_Server_Connection%25255B3%25255D.jpg"><img title="SQL_Server_Connection" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="SQL_Server_Connection" src="http://lh3.googleusercontent.com/-UQfymcj-PKk/VeGD31SUqgI/AAAAAAAAC4I/gqJVzbZKTRw/SQL_Server_Connection_thumb%25255B1%25255D.jpg?imgmax=800" width="459" height="484"></a></p> <p> </p> <p>Now once connection is setup. Now we have to export a particular table data in a flat file. but you are wondering which table or data which we are going to export.</p> <p><strong>Step 5:- </strong>Now to select data which whether it is entire table, or stored procedure output , or view output or just simple SQL query. for this we need to double click on OLEDB data source and then we will get following screen.</p> <p><a href="http://lh3.googleusercontent.com/-z0iF7piTb3U/VeGD4zUNG3I/AAAAAAAAC4Q/Dr4tDOZDthk/s1600-h/OLEB_Data_Source%25255B3%25255D.jpg"><img title="OLEB_Data_Source" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="OLEB_Data_Source" src="http://lh3.googleusercontent.com/-4z78P9rYLpk/VeGD5ism9kI/AAAAAAAAC4Y/LvyPy8ukT-k/OLEB_Data_Source_thumb%25255B1%25255D.jpg?imgmax=800" width="553" height="484"></a></p> <p>Now ,here we can choose data access mode either table or view, or SQL command ,SQL command with variable. To make this first example easy we are choosing table or view and selecting “<strong>Product</strong> table “ in below <strong>drop down for Name of the table or the view.</strong></p> <p>Step 6:- Now once you have selected table or view you can select specific columns which we need to export in flat file. For this we have to select columns option available on left side. when you click it you will get below screen.</p> <p><a href="http://lh3.googleusercontent.com/-qZRiPoBcSjY/VeGD6-ow5VI/AAAAAAAAC4g/VoAEV4ioozI/s1600-h/Check_Uncheck_columns%25255B3%25255D.jpg"><img title="Check_Uncheck_columns" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="Check_Uncheck_columns" src="http://lh3.googleusercontent.com/-sM6JMUftie4/VeGD7vCb0OI/AAAAAAAAC4o/pdRQ3E1bYsA/Check_Uncheck_columns_thumb%25255B1%25255D.jpg?imgmax=800" width="603" height="484"></a></p> <p>As shown in above figure you can check uncheck the columns which you need to export in flat file. we can rename the column name as well (as I did standard Cost to MRP). If you see below image </p> <p><a href="http://lh3.googleusercontent.com/-UpkDaG900-w/VeGD84hQyTI/AAAAAAAAC4w/L-XptUpnkRA/s1600-h/SELECTED_Columns%25255B3%25255D.jpg"><img title="SELECTED_Columns" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="SELECTED_Columns" src="http://lh3.googleusercontent.com/-Ip95TALveyg/VeGD9-se9fI/AAAAAAAAC44/-thzojAlyic/SELECTED_Columns_thumb%25255B1%25255D.jpg?imgmax=800" width="595" height="484"></a></p> <p>Here I am not explaining errorout option in detail in general sense just think it is configuration step if something failed.</p> <p><strong>Step 8:-</strong></p> <p>Now, we have source which we need to export in flat file, for this we may require a destination file in which we can store the data. So, Now we drag drop destination control which will be a flat file destination control as shown in below figure.</p> <p><a href="http://lh3.googleusercontent.com/-KyVa5HQuD2A/VeGD-ib5oxI/AAAAAAAAC5A/IRYyYFnEduk/s1600-h/FileDestination%25255B7%25255D.jpg"><img title="FileDestination" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="FileDestination" src="http://lh3.googleusercontent.com/-XuYs3obvmzs/VeGD_X8F2nI/AAAAAAAAC5I/miOFCOESSBI/FileDestination_thumb%25255B3%25255D.jpg?imgmax=800" width="644" height="299"></a></p> <p><strong>Step 9:- </strong>Now in above image you are seeing there are 2 arrows which is just flow direction means where the data needs to flow. Obviously in our case the data needs to flow from oledb source to <strong>flat file destination. So what we need to do drag the blue arrow and release it on flat file destination as shown below.</strong></p> <p><a href="http://lh3.googleusercontent.com/-wj9gl1jmpmU/VeGEADz8YYI/AAAAAAAAC5Q/wpGz0A-uuj8/s1600-h/DataFlow_arrow%25255B3%25255D.jpg"><img title="DataFlow_arrow" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="DataFlow_arrow" src="http://lh3.googleusercontent.com/-Kr_4yfZnd7U/VeGEA3Ttw_I/AAAAAAAAC5Y/050mS-dRHkY/DataFlow_arrow_thumb%25255B1%25255D.jpg?imgmax=800" width="580" height="484"></a></p> <p> </p> <p>Step 10 :- I don’t know whether you noticed or not but let me tell you here. If you see above figure data is flowing from oledb source to flat file destination which is good but on same time there is cross image in red color which means there is some error in the control. So guess what is the error ?</p> <p>I think you picked right the destination is not configured. So to do this we need to double click the flat file destination. </p> <p>Step 11: When you double click you will get below screen. In which you need to configure the file location and file format like whether you want a delimiter file, fixed length file and many other option as shown in below figure. In our example we are using delimiter file option.</p> <p><a href="http://lh3.googleusercontent.com/-8CvVLuDgH7Q/VeGEBw0CcnI/AAAAAAAAC5g/v32dzPogtiA/s1600-h/flatFileOption%25255B3%25255D.jpg"><img title="flatFileOption" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="flatFileOption" src="http://lh3.googleusercontent.com/-Z8XZZJlqgGk/VeGEClKaVJI/AAAAAAAAC5o/gw0fkYWm5KI/flatFileOption_thumb%25255B1%25255D.jpg?imgmax=800" width="562" height="484"></a></p> <p>Step 12:- When you hit OK you will get following screen where you need to configure as shown in below figure. You need to give file location with file path. if you want different delimiter the you can choose that also. </p> <p><a href="http://lh3.googleusercontent.com/-oGso1yYbmcw/VeGEDja07KI/AAAAAAAAC5w/IRtRQDszKss/s1600-h/FlatFile_Detail%25255B3%25255D.jpg"><img title="FlatFile_Detail" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="FlatFile_Detail" src="http://lh3.googleusercontent.com/-PAungZL7QyY/VeGEEU1Mt5I/AAAAAAAAC54/YTaJiuYXakQ/FlatFile_Detail_thumb%25255B1%25255D.jpg?imgmax=800" width="546" height="484"></a></p> <p>Step 12:- Now press OK you will get flat file destination editor in which you can select mapping option and just check it for your query whether all the selected columns from source are aligning or not.</p> <p><a href="http://lh3.googleusercontent.com/-9hE8s4c5gV0/VeGEFbSUzGI/AAAAAAAAC6A/E490ODFbfqE/s1600-h/Mapping%25255B3%25255D.jpg"><img title="Mapping" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="Mapping" src="http://lh3.googleusercontent.com/-8AuPkEUNA0M/VeGEGEssSMI/AAAAAAAAC6I/FzOSSMHOWNA/Mapping_thumb%25255B1%25255D.jpg?imgmax=800" width="601" height="484"></a></p> <p>Step 12:- Once we done with this you will see the cross image in red disappear. If you are still seeing this it means there is something going wrong with configuration.</p> <p>Now if everything is good then we can run our first own created package by pressing F5 or with Start option in IDE. </p> <p>Step 13:- If everything is correct you will get right check in green apart from this you might be interested how many rows transfer from source to destination so that information also can be found. see below image for detail.</p> <p><a href="http://lh3.googleusercontent.com/-TfKvxm595-E/VeGEHHvXhnI/AAAAAAAAC6Q/8njPGmIGdIs/s1600-h/RunPackage%25255B3%25255D.jpg"><img title="RunPackage" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="RunPackage" src="http://lh3.googleusercontent.com/-q8kKR0feMd4/VeGEIA2XtpI/AAAAAAAAC6Y/ePDZVvzM6QA/RunPackage_thumb%25255B1%25255D.jpg?imgmax=800" width="621" height="484"></a></p> <p>In our case we moved 504 rows. Now lets cross check at the destination location as well whether the file is created or not with these 504 rows.</p> <p><a href="http://lh3.googleusercontent.com/-drC56uthEdw/VeGEJGifE2I/AAAAAAAAC6g/Ium8sWfTaPI/s1600-h/resultFlatFile%25255B3%25255D.jpg"><img title="resultFlatFile" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="resultFlatFile" src="http://lh3.googleusercontent.com/-ZyLFicwAvXY/VeGEKCKNe_I/AAAAAAAAC6o/qMgMkWWiLdw/resultFlatFile_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="229"></a></p> <p>WOW , we did it . We created our first simplest package which is export data from SQL to flat file.</p> <p>I hope you enjoyed the learning. In next step we will do something more advance. mean while I request you all to do same practice and try to use excel instead of flat file. </p> <p>Please do write your inputs. Let me know whether you are enjoying this series or not.</p> <p>Enjoy !!!</p> <p>RJ!!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com2tag:blogger.com,1999:blog-6538321000057298497.post-36581811116547872042015-08-24T20:07:00.000-07:002015-08-24T20:53:24.119-07:00WWH of SSIS Zero to Hero in SSIS series (How to Use SSIS ?) TIP #112<p>Hello Friends,</p> <p>Welcome, back to Zero to Hero in SSIS series<a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">(Post #110).</a> In last <a href="http://sqlraaga.blogspot.in/2015/08/wwh-in-ssis-zero-to-hero-in-ssis-series.html" target="_blank">post #111</a> ,We gone through the <strong>WW (What & Why part) of SSIS</strong> in this post we will try to understand<strong> How to start SSIS ? How to use SSIS tool ?</strong></p> <p>You can start SSIS with <strong>SQL Server Data Tools</strong> which you can find in <strong>Microsoft SQL SERVER 2012</strong> folder in start menu. </p> <p><a href="http://lh3.googleusercontent.com/-lMbdHClIDAs/Vdvmbf0e00I/AAAAAAAACxo/L8AAKEFixcQ/s1600-h/SSIS_With_SQL_SERVER_20123.jpg"><img title="SSIS_With_SQL_SERVER_2012" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="SSIS_With_SQL_SERVER_2012" src="http://lh3.googleusercontent.com/-KVzJs8weu4Y/VdvmdtohUjI/AAAAAAAACxs/k8tzbbtvo30/SSIS_With_SQL_SERVER_2012_thumb1.jpg?imgmax=800" width="397" height="484"></a></p> <p>Here only the name is different but you will find same Visual Studio IDE when you click on this SQL SERVER data tools icon.</p> <p>In the IDE when you click on <strong>New Project option</strong> from <strong>File Menu</strong></p> <p><a href="http://lh3.googleusercontent.com/-lH24tF7SGMM/VdvmeSNj_CI/AAAAAAAACx0/uhu3zeRDBmg/s1600-h/New_project_option3.jpg"><img title="New_project_option" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="New_project_option" src="http://lh3.googleusercontent.com/-kyHZWl_hoaw/VdvmfNmRkmI/AAAAAAAACx8/Z2xhK1-YG5M/New_project_option_thumb1.jpg?imgmax=800" width="644" height="263"></a></p> <p>You will get below screen in which you need to select <strong>Business Intelligence template</strong> and then select <strong>Integration Services. </strong>You will get two option<strong> “Integration Service Project” & Integration Service Import Project wizard</strong></p> <p><a href="http://lh3.googleusercontent.com/-CM0DdeZMNuY/VdvmgJMQnVI/AAAAAAAACyE/YpMBaAGNJlU/s1600-h/Ssis_templates3.jpg"><img title="Ssis_templates" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Ssis_templates" src="http://lh3.googleusercontent.com/-FxC90Srflv4/Vdvmg1FF__I/AAAAAAAACyM/sBbne0F7-RE/Ssis_templates_thumb1.jpg?imgmax=800" width="644" height="394"></a></p> <p>Now, We can select any template and proceed further. In general we select <strong>“Integration Service Project”</strong> . Here I am going to share some basic components when you try to create SSIS Project.</p> <p><strong><em>Don’t bother if you don’t understand the definition of explanation given below. I know theoretically it might be hard but practically it is much much easier. </em></strong></p> <p><a href="http://lh3.googleusercontent.com/-87j0vjfEOG8/VdvmhmrRabI/AAAAAAAACyU/vR1UN8odCQg/s1600-h/Basic_Concept%25255B4%25255D.jpg"><img title="Basic_Concept" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Basic_Concept" src="http://lh3.googleusercontent.com/-Q8iw2Z0OLmY/VdvmiCpEwTI/AAAAAAAACyc/yJFms0yVzmY/Basic_Concept_thumb%25255B1%25255D.jpg?imgmax=800" width="550" height="484"></a></p> <p><strong>1) Control Flow :- Control Flow</strong> is one of the most important component. Think this as as a container which helps in workflow. If you see below image <strong>Control Flow is first tab</strong>. It might contain tasks or container. It is helpful in sequencing of task (where task can for loop, send mail, xml process,etc.) Below is container tools which we can use.</p> <p><a href="http://lh3.googleusercontent.com/-Xx1KZjdj26c/VdvmjYCAImI/AAAAAAAACyk/N-OGFzBkCxc/s1600-h/ContainerTool%25255B3%25255D.jpg"><img title="ContainerTool" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ContainerTool" src="http://lh3.googleusercontent.com/-GL97fq7XY_w/VdvmkAwn4dI/AAAAAAAACys/vJ50n_TtoxI/ContainerTool_thumb%25255B1%25255D.jpg?imgmax=800" width="279" height="484"></a></p> <p><strong>2) Data Flow Task :- </strong>Another most important component is <strong>Data Flow Task. As </strong>the name state it is a task in which data flow. Isn’t it simple ? A data flow is part of Control Flow Task. All the major operation can be accomplished with the help of data flow task controls. When you use DFT(Data flow task) you will get various option like <strong>Data Sources</strong> (from where we need to fetch data) ,<strong>Transformation controls</strong>( Operation control like aggregation, split etc.) by which we can customize the data and last but not the least <strong>Destination </strong>in which format we need the data back like SQL Server, MYSQL, ORACLE etc. (We will discuss each DFT controls in detail in coming posts). Below is DFT tools which we will use later on.</p> <p><a href="http://lh3.googleusercontent.com/-ezk7Wqyz-OI/Vdvmkz9vqgI/AAAAAAAACy0/2gE_EZpuDu8/s1600-h/DFT_tools%25255B3%25255D.jpg"><img title="DFT_tools" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="DFT_tools" src="http://lh3.googleusercontent.com/-6PbZdt0dHxM/VdvmlnLQHOI/AAAAAAAACy8/xNguNBH4cFs/DFT_tools_thumb%25255B1%25255D.jpg?imgmax=800" width="233" height="484"></a></p> <p><strong>3) Parameters:- </strong>I am sure you are aware of this parameters. Parameters are variables which help you in execution of your business logic (it might be possible you might require or not require.). Parameter has different scope and according to our need we will use and define the scope. not to worry about this as well right now. We will discuss and see practical use in coming posts. Below is the screen from which we can add parameters if required.</p> <p><a href="http://lh3.googleusercontent.com/-x4knxrQOfHY/VdvmmQ4ayII/AAAAAAAACzE/xdTFTJiNUuY/s1600-h/Parameter_Add_screen%25255B3%25255D.jpg"><img title="Parameter_Add_screen" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Parameter_Add_screen" src="http://lh3.googleusercontent.com/-fkgxv1TLMn0/Vdvmm9SLhYI/AAAAAAAACzM/lwvSprUqXHI/Parameter_Add_screen_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="171"></a></p> <p><strong>4) Event Handlers:- </strong>Event handler is the easy way to have control over your SSIS events. We can have different events like onError, onPostExectution etc. which give us liberty to improve the reliability ,monitoring and auditing of a package closely. We will surely going to do demo for this. </p> <p><a href="http://lh3.googleusercontent.com/-ykDGJfaVQYc/VdvmnoCZmPI/AAAAAAAACzU/CvEp5OMo3ao/s1600-h/Event_Handler%25255B3%25255D.jpg"><img title="Event_Handler" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Event_Handler" src="http://lh3.googleusercontent.com/-Jt0o6VjEBuo/Vdvmp7xaPFI/AAAAAAAACzc/axvo0M9XTQo/Event_Handler_thumb%25255B1%25255D.jpg?imgmax=800" width="644" height="225"></a></p> <p><strong>5) Package :- </strong>The final output of all the above core component is a <strong>Package. </strong>In other word Package is combination of various Control flow, Data flow tasks , parameters to achieve a ETL task. Earlier the extension of package was DTS and with latest version it is DTSX. If someone ask you what you do with SSIS tool you can simply say we create Packages in which we use different control flow & task flow control and once it is completed we deploy the Package. The important point here is we create a Package and then execute it by deploying.</p> <p><a href="http://lh3.googleusercontent.com/-zsap1RsKhyM/VdvmqjJM3NI/AAAAAAAACzk/LS7xJ3fBKgM/s1600-h/DTSX_Package%25255B3%25255D.jpg"><img title="DTSX_Package" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="DTSX_Package" src="http://lh3.googleusercontent.com/-Ek1EfyKkrQ0/Vdvmrd8YViI/AAAAAAAACzs/uVAkBruaTD4/DTSX_Package_thumb%25255B1%25255D.jpg?imgmax=800" width="436" height="484"></a></p> <p> </p> <p>Now in broader way if we envision it. Below picture might help you to understand it.</p> <p><a href="http://lh3.googleusercontent.com/-TOa0xLeJttI/VdvmsLB4BXI/AAAAAAAACz0/LECfrPlIM1Y/s1600-h/Package%25255B6%25255D.jpg"><img title="Package" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="Package" src="http://lh3.googleusercontent.com/-4PUO6xynkSA/VdvmspXXtkI/AAAAAAAACz8/9x0lPq2OyWU/Package_thumb%25255B2%25255D.jpg?imgmax=800" width="644" height="340"></a></p> <p>In Next post we will go one step ahead.</p> <p>Please do provide your inputs what you are thinking so far ?</p> <p>Enjoy !!!</p> <p>RJ!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1tag:blogger.com,1999:blog-6538321000057298497.post-10556168165286181382015-08-22T02:20:00.000-07:002015-08-22T17:58:13.638-07:00WWH in SSIS ? Zero to Hero in SSIS series TIP#111<p>Dear Friends,</p> <p>As shared in last post <a href="http://sqlraaga.blogspot.in/2015/08/zero-to-hero-in-ssis-sql-server.html" target="_blank">Post #110</a>, We are starting <strong>SSIS tutorial</strong> <strong>series ( A Step by Step SSIS learning tutorial) </strong>from today. </p> <p>Before starting anything we might have some questions like <strong>What is SSIS ? Why SSIS ? & How to use SSIS</strong> ?</p> <p>In general term I called this <strong>“WWH” What, why How ?</strong></p> <p>So, the straight forward answer is <strong>SSIS is SQL SERVER Integration Service (a Microsoft tool). It</strong> generally comes with SQL Server license. Although It is not always mandatory to have this <strong>SSIS tool with SQL Server license,</strong> You can install it <strong>standalone as well</strong>.<strong> SSIS is a ETL (Extract Transform</strong> <strong>loading</strong>) tool. We can can use <strong>SSIS with IDE</strong> which<strong> provided by Microsoft</strong>.</p> <p>Now, I understand this is too straight forward and we are not able to digest easily. Let me explain it with an example.</p> <p>I am leaving in <strong>Indore </strong>a<strong> city reside in Madhaya pradesh a state in the heart of India</strong>. Suppose the<strong> Chief Minister of MP</strong> allocated some funds to Municipal corporation of each cities like Indore, Ujjain, Bhopal etc. to do whatever best they can do with money and make their city a better place.</p> <p>After few years The Chief Minister realized and thought let’s ask each cities how they utilized the money.<strong> What are the different areas on</strong> which they have work with the provided money. </p> <p>The chief minister wants consolidate reports (which we generally called MIS reports) as well as some detailed reports. </p> <p>Now, suppose different municipal corporations using different ways to restore data like Indore municipal corporation using <strong>Microsoft SQL SERVER</strong>, Bhopal municipal corporation using <strong>ORACLE</strong>, Ujjain’s municipal corporation using <strong>MYSQL</strong>, Devas municipal corporation using <strong>flat file system</strong>.</p> <p>So there might be different <strong>heterogeneous systems</strong> used to maintain the data and data would be on different verticals like Transportation (Road & Bridges), Water harvesting (River & tanks) ,Agriculture , lighting etc.</p> <p>To grab this entire data & consolidate them in one single database and providing the reports to Chief minister so that he can analyze which city doing progress , which city consuming money , how much work done in different vertical so that , he can take further decisions.</p> <p>I hope you understand a big problem here which is <strong>gathering data from different heterogonous resource & compile</strong> them and provide a unique data base on which reports will create is a big task which can be easily done by an <strong>ETL (Extract Transform Loading) tool</strong> which is <strong>SQL SERVER Integration Service Tool.</strong></p> <p>Here Extract means extract or <strong>fetch data from different resource or data source whether it is SQL SERVER, ORACLE, MYSQL</strong> , Flat file or any other source</p> <p>Transform means <strong>converting the data in to required single format</strong> </p> <p>Load means <strong>prepare the data and provide final output.</strong></p> <p>Below is the image which illustrate same thing which we discussed earlier in this post.</p> <p><a href="http://lh3.googleusercontent.com/-ozkOoO9V9wE/Vdkaoez8-aI/AAAAAAAACxA/-9G6dh8RjqU/s1600-h/ETL_First_image_By_Indiandotnet3.jpg"><img title="ETL_First_image_By_Indiandotnet" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ETL_First_image_By_Indiandotnet" src="http://lh3.googleusercontent.com/-6nqZEkeKxek/VdkapOsHyMI/AAAAAAAACxI/jkKnVzJ--_I/ETL_First_image_By_Indiandotnet_thum.jpg?imgmax=800" width="471" height="484"></a></p> <p>So , I hope you understand the <strong>SSIS (ETL) role here</strong>.</p> <p>Remember here SSIS use here to provide the data only rest work will be done by <strong>SSAS (SQL Server Analysis Services) and SSRS (SQL SERVER Reporting Services)</strong></p> <p>In this post we got 2 answers <strong>1) What is SSIS ? 2) Why is SSIS?</strong> we will see<strong> How to use SSIS</strong> in next post.</p> <p>Till than</p> <p>Enjoy !!!</p> <p>RJ !!!</p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com4tag:blogger.com,1999:blog-6538321000057298497.post-65004844052698026252015-08-17T21:12:00.001-07:002015-08-17T21:12:58.353-07:00Zero to hero in SSIS (SQL SERVER Integration Services) TIP #110<p>Recently, Many friends of mine are interested in learning SSIS so , I thought to write some blogs which might help them.</p> <p>I selected following area on which I will write blog on SSIS. It would be great if you also share your inputs.</p> <p><strong>Step 1: WWH of SSIS & Architecture of SSIS ? (What , Why, How ) </strong></p> <p>Once you understand What, Why, How you are curious to know how to create a basic program /package in SSIS. I am sure you might have used this basic SSIS package but you might not aware (Import/Export) in SQL Sever.</p> <p><strong>Step 2:- How to create my first basic package using SSIS ?</strong></p> <p>With step 2 you will be more comfortable and confident that you can create package. Once your first package is created the next step which I think is deployment. How to deploy in real world and use it.</p> <p><strong>Step 3:- How to deploy SSIS package and different way of deploying ?</strong></p> <p><strong>Step 4:- How to create basic data flow task package ?</strong></p> <p><strong> With data flow task we will try to understand below common controls as well</strong></p> <p><strong> 4.a – Data Conversion</strong></p> <p><strong> 4.b - Conditional Split</strong></p> <p><strong> 4.c – Derived column</strong></p> <p><strong> 4.d – Lookup</strong></p> <p><strong> 4.e – Merge </strong></p> <p><strong> 4.f – Merge Join</strong></p> <p><strong> 4.g- Multi cast</strong></p> <p><strong> 4.h – Row Count</strong></p> <p><strong> 4.I – Sort</strong></p> <p><strong> 4.j – Union all</strong></p> <p><strong> 4.K – Others (remaining)</strong></p> <p><strong></strong> </p> <p><strong>Step 5:- Understand For Loop Container </strong></p> <p><strong>Step 6: Understand for each Loop container </strong></p> <p><strong>Step 7: Sequence container </strong></p> <p><strong>Step 8:- Bulk Insert Task</strong></p> <p><strong>Step 9:- Script Task </strong></p> <p><strong>Step 10:- Web service Task </strong></p> <p><strong>Step 11: XML Task </strong></p> <p><strong>Step 12:- File System Task </strong></p> <p><strong>Step 13:- Execute Process task </strong></p> <p><strong>Step 14:- WWH variables? How to define application variable ?</strong></p> <p><strong>Step 15:- Performance improvement of SSIS Package </strong></p> <p><strong>Step 16:- Interview questions related to SSIS</strong></p> <p><strong></strong> </p> <p><strong>I hope with above blog steps those my friends will be benefited.</strong></p> <p><strong> </strong><strong>I appreciate your inputs as well what else we can include in this series .</strong></p> <p><strong>Enjoy !!!</strong></p> <p><strong>RJ !!! </strong></p> Anonymoushttp://www.blogger.com/profile/10272758489608834274noreply@blogger.com1