Importing Excel data into SQL table via Script component

Question

I have a large excel file with different data such as text, date, currency etc. I found data conversions and derived columns not very helpful as i need to do lot of conversions and add new output fields. As i'm good at C# it is very easy to do the required transformations using the script component. So what i want to achieve is

Excel file -> Script Componenet -> SQL Table

I know how to add output columns to the script component, But i can not find a way to hook excel to the script component. How do i link Excel source and read all the excel data in the script component, with out opening and reading the excel file within the script component.

please advise. many thanks John

score 2 · Accepted Answer · answered Sep 23 '14 at 19:20

2

Try ..

enter image description here

For Script Component ..

enter image description here

Add any derived columns that you might need.

enter image description here

In your Script Editor, for each row, you can reference your input columns and derived output columns ..

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    /*
      Add your code here
    */
    string DerCol1 = "";

    if (Row.Col1_IsNull == true)
    {
        Row.DeriveCol1 = DerCol1;
    }
    else
    {
        Row.DeriveCol1 = "Add some text";
    }

}

answered Sep 23 '14 at 19:20

Tak

1,561
1
9
8

How can i get all excel columns as an output of script component. because i need all excel columns to be added to my destination. – madan V Oct 07 '14 at 06:00
@madanV .. If you are not going to add additional logic to your excel columns then you do not need to add as output in the script component. Just select the columns you need from Excel Source task. If you need more help, I would suggest to create a new question. – Tak Oct 07 '14 at 14:03

score 0 · Answer 2 · answered Sep 23 '14 at 18:28

0

Start with a DataFlow task. Add an Excel Source component, and follow that with your script component. Your script component can either be a destination unto itself, or it can be a transformation that is followed by a SQL or OLEDB Destination.

answered Sep 23 '14 at 18:28

Tab Alleman

31,483
7
36
52

Importing Excel data into SQL table via Script component

2 Answers2