How to read variable's value in Excel spreadsheet using SSIS

Question

NoI have an SSIS package that transfer and archive files every 4 hours. Inside of that package I have execute SQL Task that execute stored procedure in which I'm calling one column from Archive table. My stored procedure works and every time I run the project with breakpoints I can see that a variable is getting the column's value. The next step I need to do is to transfer that value into Excel Spreadsheet. For that I created Data Flow Task with Excel Source, Derived Column and Excel Destination. Excel Source File is just a temporary file with no data in it, so in Derived Column task I'm replacing 'F1' column with user variable and in Destination File I'm mapping that value with the right column. Every time I run the package I get a successful run, but my Destination File is empty. I don't know what I'm doing wrong, I tried every possible option but it doesn't work.

[

KeithL · Accepted Answer · 2018-11-02 14:39:48Z

1

Here is the code for @Hadi's answer:

Using Script Component Source.

Add your variable to the read variables
Add your output Column Name to Output0 output.

Add the following code to create your data to process

Output0Buffer.AddRow();
Output0Buffer.[Your Column Name] = Variables.[Your Variable name];

And you are done!!!

edited Nov 2, 2018 at 14:39

answered Nov 2, 2018 at 12:27

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hadi Over a year ago

Well done. Now the OP has a nice and complete answer.

Hadi · Accepted Answer · 2018-11-01 19:56:29Z

1

As you said

Excel Source File is just a temporary file with no data in it.

The data flow task is used to transfer data from a source to a destination. If the source is empty then no rows are transfered.

You should replace Excel source and derived column with a script component that generates rows based on the variable that stores the value you need.

answered Nov 1, 2018 at 19:56

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Collectives™ on Stack Overflow

How to read variable's value in Excel spreadsheet using SSIS

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related