CODEWITHSUNDEEP
sqlssis
d_anass
d_anass

Reputation: 131

SSIS update a column with a result of an sql select

I was wondering if there's a way of updating a column inside a dataflow task by running a select on every row?

Here's the situation :

enter image description here

Let's take this as our start position. I collect info from 2 files, then I merge them, and I add a column with the derived column tool. Is there a way of populating this column by performing a select on every row using the values of the the row?

Ex : 

SELECT Count(*) AS cnt
FROM TABLE T 
WHERE T.COLUMN1 = ROW.COLUMN3 
AND T.COLUMN2 = ROW.COLUMN5

I don't know if I'm just not phrasing my need properly but I couldn't get any results

Thank you

Upvotes: 0

Views: 1544

Answers (3)

KeithL
KeithL

Reputation: 5594

You can do that through a script component (transformation).

Add an output of Ct.

I use System.Data.OleDB as it matches the SSIS package connection string.

Upvotes: 1

d_anass
d_anass

Reputation: 131

I was able to do it with a Script Component

1- I've removed the Derived Column

enter image description here

2- I've created a string variable where I stored the query whith a wildcard string to replace every value that I need to get from the row.

3- I've passed this variable allong with one containing the connecection string info to the Script Component

4- I've added a new column to the Output Columns of the Script Component

enter image description here

5- Added using System.Data.OleDb;

6- Created 2 variables : 

string jourFerieQuery;
string dbcsoledbschema;

7- Updated the PostExecute() to put the values of my SSIS variables into the script variables :

public override void PostExecute()
    {
        base.PostExecute();
        jourFerieQuery = Variables.jourFerieQuery;
        dbcsoledbschema = Variables.dbcsoledbschema;
    }

8- Added a method : 

int GetData(string cs, string query)
    {
        OleDbConnection conn = new OleDbConnection(cs);
        conn.Open();
        OleDbCommand cmd = new OleDbCommand(query, conn);

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        conn.Close();
        return (int)dt.Rows[0][0];
    }

9- Updated the Input0_ProcessInputRow(Input0Buffer Row) :

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string query = jourFerieQuery.Replace("[1]", Row.CODDEVI).Replace("[2]", Row.DATCRBEZEROCONGE.ToString());
        Row.Keep = GetData(dbcsoledbschema, query);
    }

My query returns a count that's why the method I've added returns an int

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

You should be able to do this with a Lookup Transformation.

EDIT based on comment:

If you don't want to use a lookup due to the size of the table, you can do exactly what you want with a Script Component. You can create and execute your SQL Command for each row of the dataflow just like you would in any .net application.

Upvotes: 1

Related Questions