Transfer data from Excel file to Sql Table through Sql Query and viceversa

Source & Destination Info:

1) Source Excel Sheet

    Name - “Sheet1” in “excel2sqltable.xls”

    Contains 2 columns namely Column1 and Column2

    It contains data as  1, Prakash

                 2, Bharath

                 3, Sastry

2) Destination Sql Server Table

    Name - “excel2sqltable”

    Contains 2 columns namely Column1 and Column2

Query to select the records from the excel sheet:

SELECT Column1,Column2

FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel8.0;Database=L:\practice\excel2sqltable.xls', [Sheet1$])

Query to select and insert the records from excel to table:

INSERT INTO dbo.excel2sqltable

(

Column1,

Column2

)

SELECT Column1,Column2

FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=L:\practice\excel2sqltable.xls', [Sheet1$])

** Note:

On executing the above query you may get Error :

“Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.”

To enable the property run the below query :

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

It will enable the property which allows to modify  advanced options.

Then it will enable the execution of the ‘d Hoc Distributed Queries’.

Transfer data sql server to excel sheet

Query to transfer the records from table to excel:

INSERT INTO

OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=L:\practice\excel2sqltable.xls;',

'SELECT * FROM [Sheet1$]')

SELECT * FROM dbo.excel2sqltable

Scenarios to understand Transaction Options in SSIS

Required:

Indicates that the container starts a transaction, unless one is already started by its parent container.

If a transaction already exists, the container joins the transaction.

Supported:

Indicates that the container does not start a transaction, but joins any transaction started by its parent container.

If no transaction is started by its parent container, it is not bound by any transaction.

NotSupported:

Indicates that the container does not start a transaction or join an existing transaction.

A transaction started by a parent container does not affect child containers that have been configured to not support transactions.

Practical Example info:

The package contains a FOREACH LOOP CONTAINER.

It iterates through the files in the folder and the complete file name with the path is stored in the variable “var_Filename”.

The FOREACH LOOP CONTAINER contains a DATA FLOW TASK.

It has one FLAT FILE SOURCE and one OLEDB DESTINATION.

FLAT FILE SOURCE connection string is configured with the variable “var_Filename”.

                           
             

So in every loop of the Foreachloop container the FLAT FILE SOURCE points to a new file in the folder.

It iterates through 3 “.txt” files present in a folder.

Files are ff1_TPinForeach.txt, ff2_TPinForeach.txt and ff3_TPinForeach.txt.

File “ff1_TPinForeach.txt”:

Column1

1

2

3

File “ff2_TPinForeach.txt”:

Column1

4

5

6

File “ff3_TPinForeach.txt”:

Column1

7

8

A

The data from these files are transferred to the table “TPinForeach” which contains only one column “Column1” of data type NUMERIC.

** The last row of File “ff3_TPinForeach.txt” has invalid record so as to trigger the failure of package.

Scenario 1:

Transaction Option of FOREACH LOOP CONTAINER = Supported

Transaction Option of DATA FLOW TASK = Supported

On executing the package,

-       Package fails

-       The table contains 8 records from 1-8

Reason:

Since there was no component having Transaction Option = REQUIRED, so no transaction was started at any point during the package execution.

As a result NO Rollback happened while the data was transferred from the files to the table.

Thus the table contains 8 records as the failure happened in the 9^th record.

Scenario 2:

Transaction Option of FOREACH LOOP CONTAINER = Supported

Transaction Option of DATA FLOW TASK = Required

On executing the package,

-       Package fails

-       The table contains 6 records from 1-6

Reason:

Since the Transaction Option = REQUIRED for DATA FLOW TASK, so a new transaction was started when the execution of DFT started. The transaction terminated once the execution of the same DFT was finished.

While transferring data from the file “ff3_TPinForeach.txt” into the table, the DFT failed.

Since it was part of a transaction, the data transferred in that transaction is Rolled Back.

Thus the table contains 6 records and not the 7^th and 8^th record. These 7^th and 8^th record were part of the transaction which contained Wrong 9^th record. As a Rollback functionality the 7^th and 8^th were rolled back.

Scenario 3:

Transaction Option of FOREACH LOOP CONTAINER = Required

Transaction Option of DATA FLOW TASK = Supported

On executing the package,

-       Package fails

-       The table contains 0 records

Reason:

Since the Transaction Option = REQUIRED for FOREACH LOOP CONTAINER, so a new transaction was started when the execution of FOREACH LOOP CONTAINER started.

The DATA FLOW TASK has Transaction Option = Supported. So it joined the above transaction and became part of the same transaction.

Thus when the FOREACH LOOP CONTAINER failed because of the error 9^th record, the transaction started by the FOREACH LOOP CONTAINER Rolled back. Since the DFT were part of the same transaction they rolled back completely from the beginning i.e. from record 1.

Scenario 4:

Transaction Option of FOREACH LOOP CONTAINER = Required

Transaction Option of DATA FLOW TASK = Required

On executing the package,

-       Package fails

-       The table contains 0 records

Reason:

Same as Scenario 3

References
** http://msdn.microsoft.com/en-us/library/ms137690.aspx

**   http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

** Very high level - http://msdn.microsoft.com/en-us/library/ms141083.aspx