SSIS - Excel -How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS

SSIS Part 160-Import Data from Excel Files for specific Sheet Name to SQL Table by SSIS Package

https://www.youtube.com/watch?v=S2ABF73d8QQ

~

How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package - SSIS Tutorial

http://www.techbrothersit.com/2016/03/how-to-import-data-from-excel-files-for.html

~

http://www.techbrothersit.com/

http://www.techbrothersit.com/search/label/SSIS%20Video%20Tutorial

xml - Generate XML Formatted File From SQL Server - SSIS

Generate XML Formatted File From SQL Server - SSIS

~

SELECT (

SELECT Employee.*,Dept.*  FROM  XML_EMP  Employee INNER JOIN XML_DEPT  Dept

ON Employee.DeptID=Dept.DeptID

FOR XML AUTO, ROOT('Organization') AS Data,'B:\SQLFolder\XML\Output\Test3.xml' AS Path

Execute SQL Task:

Resultset:XML

Variable:

Result:

~

TRUNCATE  TABLE  LOAD_XML_EMP  

GO

DELETE FROM LOAD_XML_DEPT

GO

note:

i. It can not use truncate as it's PK is referneced as a FK in a table.

ii. So separate XSD task is used for data flow. As  FK reference is used.

iii. If there would not have any FK reference - one DFD task would have been fine.

~

.xsd - open in VS 2008

drag the .xsd to VSS.

~

Download   XSD.EXE  from IE

C:\> xsd.exe test2.xml

1.

This will generate .xsd open in VSS: the diagram  edit the diagram.

Employee                                       Dept

Eid                                                  DeptId

Ename                                             DeptDesc

Salary

DeptId

Employee_Id             -edit                      Employee_Id (delete)

(delete -click edit)

Link table EmployeeId  - EmployeeId

(correct it.)

Relation

Employee                      Dept

Key column                  FK 

DeptId                          DeptId

2.

Add PK Constraint

Employee    Dept

Dept_Id       Dept_Id

2.Add  PK  constraint

Employee                Dept

Eid<-create pk        DeptId

Ename                     Deptname

Ename

Salary

DeptId

~

ScriptMain.cs

public void Main( )

{

    //TODO: Add your code here

string  xml_content = dts.variables("User::Var_xml_out").value.ToString( );

StreamWriter file= new  StreamWriter("B:\\SQLFolder\\XML\output\\test.xml");

file.WriteLine(xml_content);

file.close();

dts.TaskResult=(int)ScriptResults.Success;

}

ReadonlyVariable:User::var_xml_out

variable  xml 

~

SQL1:Output:xml

SELECT Employee.*,Dept.*  FROM XML_EMP_Employee INNER JOIN XML_DEPT Dept ON 

EMPLOYEE.DEPTID=DEPT.DEPTID FOR XML  AUTO,ROOT('Organization')

GO

~

SQL2:Output:string

SELECT (

SELECT Employee.*, Dept.*  FROM  XML_EMP_Employee INNER JOIN XML_dept dept on
employee.DeptId=DEPT.DEPTID
FOR XML AUTO,ROOT('Organization')) AS Data

output:
Data  Path

~
SQL1:Output:xml

SELECT (
SELECT Employee.*,Dept.*  FROM XML_EMP Employee INNER JOIN  XML_Dept dept
ON  Employee.DeptId=Dept.DeptId  FOR  XML AUTO,ROOT('Organization'))
AS Data,'B;\SQLFolder\XML\Output\Test3.xml' AS Path

~
---------------------------------------------------------------------------------------------------------------------

Method1:

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

Method2: ODBC Source :SQL 2

SQL Command

OLE DB Source

Data Conversion

FlatFile Destination

~

Flat file Connection

Filename: B:\SQLFolder\XML\Output
( create a new file format test2.xml)

Columns:
New
Columnno:XMLData
outputcolumnwidth:500
~

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

Method3:

OLEDB  Source

Data Conversion

Export Column

~

Export column  File pathcolumn

CopyofData       Path

All  App  ForceTruncate

                     --Truncate every type

xml - Loading XML Documents

SQL Server 2012 - Loading XML Documents

~

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

  <Products>

    <Product>

         <ProductID>855 </ProductID>

         <Name> T-Shirt </Name>

         <ProductNumber>1234</ProductNumber>

         <ListPrice>89.99</ListPrice>

         <ModifiedDate>    </ModifiedDate>

     </Product>

      <Product>

     </Product>

 </Products>

~

example1.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)


EXEC  sp_xml_removedocument @hdoc

example2.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)


EXEC  sp_xml_removedocument @hdoc

~------------------------------------------------------------------------------------------------------------------------

step wise:

1.

SELECT
FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

note:
i. it creates a table named "Products"  and a column named "P"  and the  'c:\Examples\Products.xml' is stored as binary.

2.

DECLARE @x xml

3.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

Now xml in sql server. It can be read using below sql.

SELECT  * FROM OPENXML(@hdoc, ' ',1) WITH ( )

Treats the xml as the table.

@hdoc - handle to the xml doc.

'  '  - xpath ( xml hierarchy u want to select data from ).

1,2  - flag

1 - attribute
2 - fields are elements

4.

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

5.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)

EXEC  sp_xml_removedocument @hdoc

6.

a.No rows will be returned.

SELECT * FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product',1)

note: 1 , look for attribute not element.

WITH (

ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
Modifieddate datetime

)

b.Rows will be returned.

SELECT  *  FROM  OPENXML ( @hdoc, 'Subcategories/Subcategory/Products/Product',2)
WITH (
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
ModifiedDate datetime
)

7.

a.

SELECT  *  INTO  MySubcategories FROM OPENXML(@hdoc, '/Subcategories/Subcategory',1)
WITH(
ProductCategoryID int,
Name varchar(100)
)

b.

SELECT  *  INTO  MyProduct FROM OPENXML(@hdoc, '/Subcategories/Subcategory/Products/Product',2)
WITH(
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float
)

8.

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)

note:

flag : 2 - elements , so to include attribute  each level is defined as ../

Name,ProductName is different otherwise duplicate error would have come.

xml - Load XML Files into SQL Server Tables - SSIS

Load XML Files into SQL Server Tables - SSIS

Process Multi-Level XML in SSIS

Process Multi-Level XML in SSIS



It has 4 nodes. So if  this xml file to "source" it will have 4 output.

we want one row, all book related information in one row.

Just like a table structure.

Meaning of -

Meaning of - <?xml version=“1.0” encoding=“utf-8”?>

.....

This is the XML optional preamble.

• version="1.0" means that this is the XML standard this file conforms to
• encoding="utf-8" means that the file is encoded using the UTF-8 Unicode encoding

~

Think of XML as not a sequence of characters but a sequence of bytes.

Imagine the system receiving the XML sees the bytes 195, 162. How does it know what characters these are?

In order for the system to interpret those bytes as actual characters (and so display them or convert them to another encoding), it needs to know the encoding used in the XML.
~

To understand the "encoding" attribute, you have to understand the difference between bytes andcharacters.

Think of bytes as numbers between 0 and 255, whereas characters are things like "a", "1" and "Ä". The set of all characters that are available is called a character set.

Each character has a sequence of one or more bytes that are used to represent it; however, the exact number and value of the bytes depends on the encoding used and there are many different encodings.

Most encodings are based on an old character set and encoding called ASCII which is a single byte per character (actually, only 7 bits) and contains 128 characters including a lot of the common characters used in US English.

For example, here are 6 characters in the ASCII character set that are represented by the values 60 to 65.

Extract of ASCII Table 60-65
╔══════╦══════════════╗
║ Byte ║  Character   ║
╠══════╬══════════════║
║  60  ║      <       ║
║  61  ║      =       ║
║  62  ║      >       ║
║  63  ║      @       ║
║  64  ║      A       ║
║  65  ║      B       ║
╚══════╩══════════════╝

In the full ASCII set, the lowest value used is zero and the highest is 127 (both of these are hidden control characters).

However, once you start needing more characters than the basic ASCII provides (for example, letters with accents, currency symbols, graphic symbols, etc.), ASCII is not suitable and you need something more extensive. You need more characters (a different character set) and you need a different encoding as 128 characters is not enough to fit all the characters in. Some encodings offer one byte (256 characters) or up to six bytes.

Over time a lot of encodings have been created. In the Windows world, there is CP1252, or ISO-8859-1, whereas Linux users tend to favour UTF-8. Java uses UTF-16 natively.

One sequence of byte values for a character in one encoding might stand for a completely different character in another encoding, or might even be invalid.

For example, in ISO 8859-1, â is represented by one byte of value 226, whereas in UTF-8 it is two bytes: 195, 162. However, in ISO 8859-1, 195, 162 would be two characters, Ã, ¢.

Think of XML as not a sequence of characters but a sequence of bytes.

Imagine the system receiving the XML sees the bytes 195, 162. How does it know what characters these are?

In order for the system to interpret those bytes as actual characters (and so display them or convert them to another encoding), it needs to know the encoding used in the XML.

Since most common encodings are compatible with ASCII, as far as basic alphabetic characters and symbols go, in these cases, the declaration itself can get away with using only the ASCII characters to say what the encoding is. In other cases, the parser must try and figure out the encoding of the declaration. Since it knows the declaration begins with <?xml it is a lot easier to do this.

Finally, the version attribute specifies the XML version, of which there are two at the moment (see Wikipedia XML versions. There are slight differences between the versions, so an XML parser needs to know what it is dealing with. In most cases (for English speakers anyway), version 1.0 is sufficient.

~

How many bits in a character?

It depends what is the character and what encoding it is in:

• An ASCII character in 8-bit ASCII encoding is 8 bits (1 byte), though it can fit in 7 bits.
• An ISO-8895-1 character in ISO-8859-1 encoding is 8 bits (1 byte).
• A Unicode character in UTF-8 encoding is between 8 bits (1 byte) and 32 bits (4 bytes).
• A Unicode character in UTF-16 encoding is between 16 (2 bytes) and 32 bits (4 bytes), though most of the common characters take 16 bits. This is the encoding used by Windows internally.
• A Unicode character in UTF-32 encoding is always 32 bits (4 bytes).
• An ASCII character in UTF-8 is 8 bits (1 byte), and in UTF-16 - 16 bits.
• The additional (non-ASCII) characters in ISO-8895-1 (0xA0-0xFF) would take 16 bits in UTF-8 and UTF-16.

That would mean that there are between 0.03125 and 0.125 characters in a bit.

SSIS - Using Temp Tables in SSIS

Using Temp Tables in SSIS

Temporary
tables are created in the TempDB database, which persists for a particular session. The
objective is to maintain that session, until the temp table information
is used and dump the data into a physical table. To maintain a session in SSIS, there is a
‘Retain Same Connection’ property of the Connection Manager. If we need to
maintain a single session, we have to mark this property ‘True’.

Let us
start with a sample package. I will create the package step by step.

1. Create two Connection manager’s – ‘Server 1′ and ‘Server 2′. Server 2 will be
   the server where we need to create the temp table using Server 1 and thereafter use that
   temp table in the queries executed on Server2.
   
2. Set
   the RetainSameConnection property of the Server 2 to True.
   
3. Drag an
   Execute SQL Task in the Control Flow.
   
4. Insert
   the SQL Statement create table #temptestusers (id int) inside the
   SQLStatement field in the Execute SQL Task Editor. This will create the temp table. Also set the Connection to
   Server2.
   
5. Drag a
   DFT below the Execute Sql Task, which will insert the data in the temp table.
   
6. Drag
   OLEDB source and OLEDB destination in the DFT
   
7. In the
   OLEDB Source, enter the query

   SELECT 1 as id
   UNION ALL
   SELECT 2 as id
   

   
8. The next
   step is to connect the ‘OLEDB Destination’.  Our objective is to load the data
   into a temporary table. However, you will not be directly able to select the temp
   table in Design mode. So, we will create a new temp table, if you do not have
   table creation rights, you can create a global temporary table and select
   that. Here, we will create a TestDestination table and map the Source and
   Destination columns :
   
9. Now, as we mapped it in design mode, we can modify it
   later to use the Temporary table. To use Temporary table in the Destination, follow
   the below steps:
   a) Go to SSIS Menu > Select Work Offline
   
   o
   b) Select the OLEDB Destination > Properties >
   Set the OpenRowset property to use the temporary table ‘dbo.[#temptestusers]‘
   
10. Now, we can verify the data in the temp table. First of
    all Deselect the ‘Work Offline’ mode, which was used in above step. To verify
    we will add other Data Flow Task which will query this temp table created above
    and will load the data from it to another server table. Add a  second DFT, ‘Use
    the temp table and insert in physical table’.
    
11. Add OLEDB Source and Destination in the new DFT. Use
    SQL to select the rows from temp table select id from
#Temptestusers.
    
12. You will not be able to select it in design mode, as the
    temp table does not exist right now. So, first select from any other table and
    start the DFT working, then follow the same steps as shown above. ‘Go to
    the SSIS menu -> Select Work Offline -> Go to the OLEDB Source Properties
    -> Select SQLCommand and modify the query in the String Value editor to use
    the temp table.
    
13. Next in the OLEDB Destination, select the server on which
    you have permission to create table, in this case I am selecting Server1.
    
14. That’s it. The temporary table package is now completed.
    We can run the package and review the flow of data.
    
    

    2 Rows transferred into #temptestusers table.

    

    2 rows transferred to the physical table from temp table.

Hopefully this will help anyone to make use of
temporary tables in their design. The only thing is to make sure of is that we set
‘Work Offline’ flag on/off in the package.