regarding datamigration using ssis packages

hai,

Currently i am working with data migration using SSIS packages.

i would like to load the data from the flat file to database(siebel).

But i need three extra columns which are not there in flat file to load into the database.

the columns are as follows.

1.ROW_BATCHfixed value(eg:100)

2.ROW_ID-(should be unique just like a sequence)

3.ROW_STATfixed value(string)

The flatfile columns along with the above specified columns should be loaded into the siebel database using SSIS package.

please help me.

You can use a Script-Component in the DataFlow-Task:

1. Drag and configure a Flat-File-Connector to a DataFlow-Task.

2. Drag a Scrip-Component to your DataFlow-Task and select transformation.

3. Connect the Output of your Flat-File-Connector to thr Script-Component.

4. Double-Click the Script-Component and select thr Input and Outputs

5. Add 2 new columns. The new column Fix has the DataType string. The new column ID has the DataTyp [DT_i4].

6 Navigate to the Script tab and Click Design Script.

7. Edit your Script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim id As Integer = 0

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Row.Fix = "123"
id = id + 1
Row.ID = id
End Sub

End Class

8. Connect the Output of the Script-Component to your destination.

|||thanks loom , thank u very much for your help

Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql