Write into Excel Destination from SSIS variables

2
votes

I have 3 SSIS variables namely name, age, gender with initial values set. I want to write these values into excel sheet in one row. Later I will extend this to Array of records.

To do this I have created Excel connection attaching the excel sheet where I want to write.

I added control flow task and double clicked and then added Derived column component to create derived columns for each of above 3 variables . Inside derived column editor I selectd above variables as new derived columns.

And then pipelined excel destination component and mapped sheet columns to derived columns. I executed the SSIS package and its successful. But variables are not written into excel sheet.

What I am doing wrong ?

3
ssis
What is your data source in this Data Flow? Where rows of data are coming from? – Ferdipux
I have no data source component, data is present in SSIS variables – Satishakumar Awati
I believe you need a source component so rows start the flow from there. Derivated column is used to apply transformations or add new columns, but not completely new rows. Once executed, can you see the amount of rows in the link between the derived column and the excel destination? If not, it's 0 rows. – EzLo
I added data viewer it does not show up anything, just green ticks – Satishakumar Awati
Added a new answer using script component as your source @EzLo – KeithL

3 Answers

5
votes

Again, you need a source. I gave you an "easy" solution. This is probably the best solution to your problem:

This time the source will be a script component (select Source).

Steps after you add Script Component:

  1. Select Source
  2. Go to Inputs and Outputs
  3. Add your Output Columns (Don't forget about data types)

Output Columns Image

  1. Go back to Script
  2. Add you variables (Gender, Name and Age)

Image adding variables

  1. Go into Script

  2. Add the following code

    public override void CreateNewOutputRows()
{
   Output0Buffer.AddRow();
   Output0Buffer.Age = Variables.Age;
   Output0Buffer.Gender = Variables.Gender;
   Output0Buffer.Name = Variables.Name;
}
1
votes

You need a source. the easiest would be to use a SQL connection.

Use a variable of type string named SQL.

Set SQL = "Select '" + name+ "' as name,"+ age + "as age,'" + gender + "' as Gender

Set your source to SQL variable.

Connect this Source to Destination and you should have 1 row with 3 columns

1
votes

Listing the steps clearly as suggested by @KeithL

  1. Create a SSIS variable selectQueryVariables with string datatype.

  2. Assign variable expression as 

    "SELECT '"+@[User::name]+"' as Name,'"+@[User::gender]+"' as Gender,"+(DT_WSTR,4 )@[User::age]+" as Age"

  3. Add OLE DB Source component and set data access mode as SQL command from variable and select the variable selectQueryVariables in dropdown. Now the source is ready with 3 columns Name, Age and Gender.

  4. Pipeline this with Excel Destination and map columns source and destination.