2016-SSIS-1-Import data from excel into SQL Server using SSIS

Import data from excel into SQL Server using SSIS

a.xls <- not Microsoft latest versn. The latest is .xlsx

Id    Name
1     Keshav
2     Gaurav
3     Kalpana

CREATE TABLE EMP_A(Id INT,Name NVARCHAR(500),Dept VARCHAR(10) DEFAULT 'IT')

CREATE TABLE EMP_B(Id INT,Name NVARCHAR(500),Dept VARCHAR(10) DEFAULT 'HR')

Create an Integration Services project.

In Solution Explorer-Properties-Debugging -
Run64BitRuntime - false


ControlFlow:

Sequence Container

DataFlow Task<-Property(DelayValidation=True)
                                     




Right Click - when in the control flow tab.Choose Variable.
Variables:

Name          Scope        Data Type      
ExcelPath    Package    String
TableName  Package     String

Value
C:\Keshav\A\a.xls
EMP_A





To make package dynamic , in the "Data Flow Task" ,properties, the "DelayValidation" property is set to "True".



The prevalidation check will not happen, the values will be checked at the run time ( for the package path, table name).

----
Data Flow Task:DFT




In the Data Flow Task , "ValidateExternalMetadata" , set it to "False".

Note:
In the Control , it is set to "DelayValidation" True , in the Data Flow Task in the "Excel Source", "ValidateExternalMetadata" to false,the equivalent.

Right Click - "Excel Source" - Choose "Edit".Then set up the configuration details.




















Connection Manager:
Excel Connection Manager <-Right click - choose "Expression", choose
the "ExcelFilePath" from the drop down box.





Drag the OLEDB Destination.Set the "ValidateExternalMetaData" to False..

Right Click on the OLE DB Destination <- Edit







Dynamic:

Right click->Package Configurations







Click on Browse:->



Type the .Config name that you want to create for this xml configuration.



What things we want to make dynamic, the variable values.







Quick tip:to look at the dtsconfig file path.



Run command:paste it





Open the same file in notepad.



To Deploy the package.
On the solution explorer - properties- CreateDeploymentUtility-True


Build the package.Under "Bin" directory , creates a "Deployment" folder.

Deployment folder has 3 files.



Deploy using the Manifest file.


Double click the Manifest file, it will ask "File System" or MSDB.



yes







Folder content what is there?

SQl- Difference Between Char, Nchar, Varchar and Nvarchar Data Types in SQL Server

Difference Between Char, Nchar, Varchar and Nvarchar Data Types in SQL Server

This small article is intended for the audience stuck in their interview when asked for the differences among CHAR, VARCHAR, NCHAR and NVARCHAR data types. Actually it is simple but sometimes people get confused.

To store data as characters, numeric values and special characters in a database, there are 4 data types that can be used. So what is the difference among all 4 of these data types?

• CHAR vs VARCHAR
• NCHAR vs NVARCHAR

Considering an example, we will look into each one of them. 

1. DECLARE @string CHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'  

Note: The LEN() method provides the length of a character excluding trailing blanks stored in the string expression whereas the DATALENGTH() method provides the number of byte spaces occupied by the characters in a string expression.
 
As you know we represent the character values within single quotes, for example 'Robin'. But do you know we can represent these same characters within double quotes similar to programming languages representing a string, for example “Robin”? This can be done by setting the value:

1. SET QUOTED_IDENTIFIER OFF  

By default, it is set to ON
 
CHAR vs VARCHAR

Talking about the CHAR data type:

• It is a fixed length data type
• Used to store non-Unicode characters
• Occupiers 1 byte of space for each character

If the value provided to a variable of CHAR data type is shorter than the length of a column of declared the size of the variable, then the value would be right-padded with blanks to match the size of column length. 

1. DECLARE @string CHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

As you can see above, the bytes occupied by the variable are 20 even though the length of the characters is 5. That means that irrespective of the character stored in the column, it will occupy all bytes to store the value.

About the VARCHAR data type:

• It is a variable length data type
• Used to store non-Unicode characters
• Occupies 1 byte of space for each character

1. DECLARE @string VARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

As you can see above, it is showing DATALENGTH as 5 which means it will use only the number of bytes equal to the number of characters. This will allow me to avoid wasting database space.

Note:  If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a CHAR column defined as NULL is considered as VARCHAR.

When to use what?
If you are sure about the fixed length of the data that would be captured for any specific column then go for CHAR data type and if the data may vary then go for VARCHAR. 
 
NCHAR vs NVARCHAR
Similar to CHAR data type, the NCHAR data type:

• Is a fixed length data type
• Used to store Unicode characters (for example the languages Arabic, German and so on)
• Occupies 2 bytes of space for each character

1. DECLARE @string NCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

As you can see above, the data length column shows 40 bytes even though the size declared is 20. It's because NCHAR holds 2 bytes of space for each character.

About the NVARCHAR data type:

• It is a variable-length data type
• Used to store Unicode characters
• Occupies 2 bytes of space for each character

1. DECLARE @string NVARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

As in the output above, you will observe DATALENGTH column is showing only 10 as a value. That is because it occupies 2 bytes of space for each character and the data length is only 5 characters, therefore it will occupy 10 bytes of space in the database.

When to use what?
If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR. 

Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that. 

ConclusionThis small article is just to make you aware of the differences among the CHAR, VARCHAR, NCHAR and NVARCHAR data types since they are all used to store characters, numbers or special characters. I hope you like this small article and will that it will be helpful to you at some point of time. Leave your comments whether its good or bad. Sharing is valuable no matter what :)

SQL - case statement

Understanding Case Expression in SQL Server with Example

1. -- Simple CASE expression:
2. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
3. FROM dbo.Customer
4. GROUP BY StateCode,Gender,FirstName
5. HAVING (MAX(CASE Gender WHEN 'M'
6. THEN PayRate
7. ELSE NULL END) > 180.00
8. OR MAX(CASE Gender WHEN 'F'
9. THEN PayRate
10. ELSE NULL END) > 170.00)
11.  
12. -- Searched CASE expression:
13. SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
14. FROM dbo.Customer
15. GROUP BY StateCode,Gender,FirstName
16. HAVING (MAX(CASE WHEN Gender = 'M'
17. THEN PayRate
18. ELSE NULL END) > 180.00
19. OR MAX(CASE WHEN Gender = 'F'
20. THEN PayRate
21. ELSE NULL END) > 170.00)

COALESCE and ISNULL

Source:

COALESCE and ISNULL

According to SQL Server Books Online, COALESCE "returns the first nonnull expression among its arguments," and ISNULL "replaces NULL with the specified replacement value." As a simple example, the following code demonstrates using the two functions:

SSIS - Send Mail - Script Task email setting - attach Zip file - Xml content

Send Mail - SSIS

https://www.youtube.com/watch?v=1lrvm-iqFVI

Send Mail Task:->

~

General

Mail

Expression

~

Mail

SmtpConnection

From 

To 

Cc

BCcc

Subject

MessageSourceType  Direct Input

MessageSource  <--Message that you want to send ->

Priority Normal

Attachments <-Attachment of the file you want->

Note:

i.Send Mail Task -> sends text emails

But if you need to send  "HTML"  format Send Mail Task can not send.

If you want attach Zip file also it can not send.

Security:

As you are using "SmtpConnection"  , it takes your own credential.

When you move to production the "Service Account" that you use need to have permission to send email.

-----------------------------------------------------------------------------------------------------------------------------------------------

This session is using script task.

Script Task:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.windows.Forms;

using System.IO;

using System.Net;

using System.Net.Mail;

public void Main( )

{

string Sender = "entersenderemailaddress";

string Recipient = "enterrecipientemailaddress";

string SMTP="smtp.live.com";//"smtp.gmail.com";

string Body="Message that you want to send";

Attachment Attach= new Attachment("C:\\yourfilepath\\test2.zip");

MailMessage msg= new MailMessage(Sender,Recipient,"Attached Org XML Data", Body);

SmtpClient smtpClient=new SmtpClient(SMTP,25); // 25 -- SMTP Port

smtpclient.EnableSsl= true;

smtpclient.UseDefaultCredentials = false;

System.Net.NetworkCredential credentials = new NetworkCredential(Sender ,"DV6226TX"); // "DV6226TX" - current password of the email account

smtpclient.UseDefaultCredentials = false;

smtpclient.Credentials=credentials;

msg.Attachments.Add(Attach);

smtpclient.Send(msg);

Dts.TaskResult = (int) ScriptREsults.Success;

}

Learning XML Tutorials

Learn XML in 30 days



                                             XML Tutorials

coursera-Stanford University-Introduction to Databases

                                                              XML Data:

                                                              NoSQL Systems 

Learning SQL Server tutorial for beginners

SQL Server tutorial for beginners

135 videos

SQL Server Interview Questions and Answers

13 videos

Learning .NET & C#(Csharp)

c# tutorial for beginners

100 videos.

ASP.NET tutorial for beginners

171 videos.