SSIS - EXCEL

Ma Gurudeva

Generate Custom Spreadsheet & Workbook Excel Via SSIS Keshav Singh

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

Requirement:

Data is in two tables.

Select * from Employee

Select * from DeptInfo

In Excel: In the respective tab the data should show.

Name:org_employee.xlsx

Two tabs:

Employee  Deptinfo

SSIS:

Control Flow:

Generating Custom Spreadsheets and Workbook

Script Task

Execute SQL Execute SQL

Task Task 1

Data Flow Task(Properties:DelayValidation=True)

Data Flow Task:

--

Data Flow Task:

OLE DB Source 1 OLE DB Source

Data Conversion 1 Data Conversion

Excel Destination 1 Excel Destination

(properties:

ValidateExternalMetadata:false)

Generate Custom Spreadsheet & Workbook Excel Via SSIS Keshav Singh

--

Step:

Right Click->Choose "Variable"

Create one variable.

Name Scope Data Type Value

var_dir localExcel String B:\SQLFolder\SSIS\Excel\1

--

Connection Managers:

Excel Connection Manager Server

~

Server:

Server name: sql server name

Connect to Database

- Select or enter database name: database name

Test Connection

~

Excel Connection Manager:

Excel file path:

B:\SQLFolder\SSIS\Excel\1\org_employee

Excel version:

Microsoft Excel 2007

[.]First row has column names

~

--

SSIS Package:

--

Script Task: Delete any existing "org_employee.xlsx" file.

Script Task Editor:

Script:

Script

Script Language Microsoft Visual C# 2010

EntryPoint Main

ReadOnlyVariables User::var_dir

ReadWriteVariables

Edit Script ( click on it)

public void Main()

{

// TODO:  Add your code here

string  dir = Dts.Variables["User::var_dir"].value.ToString() + "org_employees.xlsx";

if (file.Exists(dir))

File.Delete(dir);

Dts.TaskResult = (int)ScriptResults.Success;

}

---

Execute SQL: Creating the workbook excel file format( same as the Schema of the table)

ConnectionType:EXCEL

Connection:Excel Connection Manager

SQLSourceType:Direct import

SQL Statement:

CREATE TABLE  'Employee'(

'EmployeeNumber' int,

'EName'  varchar(10),

'Skill'     varchar(10),

'City'         varchar(10),

'Doj'     diatomite

)

Execute SQL1: Creating the workbook excel file format( same as the Schema of the table)

--

Data Flow Task:

OLE DB Source 1 OLE DB Source

Data Conversion 1 Data Conversion

Excel Destination 1 Excel Destination

~

OLE DB Source 1

Connection Manager

Columns

Error Output

OLE DB connection manager:

Server

Data access mode:

SQL command

SQL command text:

Select  *  FROM DeptInfo

Data Conversion 1:

Input Column Output Alias Data Type Length

Ename Copy of Ename Unicode string[DTWSTR] 10

DeptDesc Copy of DeptDesc Unicode string[DTWSTR] 20

Excel Destination 1:

Connection Manager

Excel connection manager:

Excel Connection Manager

Data access mode:

Table or view

Name of the Excel sheet:

DeptInfo$

~

--

As  it is excel.Need to make the 64 bit Runtime:false

Solution Explorer:

         DataHashing <- Right click on "Solution name" - choose "Properties"

Common Properties

Configuration Properties

Build

Deployment

Debugging<-  

Debug Options

...

Run64BitRuntime:False

---

We have created a variable at the top.

"Variable": var_dir

If  we want to take the value from this variable as user will change as per requirement.

Connection Managers:

Excel Connection Manager<- Right click properties

Expression:

Property Expression

ExcelFilePath @[User::var_dir] + "org_employees.xlsx"

---

Missed: mentioned:overwrite the Excel Connection Manager: with the variable