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

refresh SSIS pkg to get the change

Hi, all,

How do you refresh a SSIS pkg to get the latest table schema change?

I have this data flow task that will load data from a flat file into a table.

I got a Warning: Truncation may occur due to retrieving data from database column "txtSNumber" with a length of 50 to data flow column "txtSNumber" with a length of 20.

Then I went into Management Studio and changed the column size.
Now in my dev BID I got next:
[SQL Server 05[82]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "txt..." needs to be updated in the external metadata column collection.

I think this means my change on the table did not get into my ssis, and I could not find a way to refresh.

Thanks!It actually means the opposite. The change was detected by SSIS and it needs to update the metadata associated with the table in the data flow task (i.e. change the column width from 20 to 50). It does not automatically do this without some user interaction (hitting "OK") since you may need to know about the change and correct it if it is incorrect.|||

Thanks for the reply.

I am not looking for the automatic way to refresh if there is one.

I want to manually start the process in my design environment and click the OK as you have suggested, but I could not find out how or from where to make it happen.

I have tried right click on my .dtsx and select reload with upgrade, but that is not it. Where can tap into the megadata?

|||Look at the components in your data flow that have the yellow triangle with an exclamation mark in it. Start there by double clicking on that component to refresh the metadata.|||Double click on the yellow mark is the trick! Thanks!