DataFlow suggestion

I am transfering data from a textfile to sql server.I use a data flow task for trasfering my text files.

Here is what i do.

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

please help...

Not sure without looking at your data, but look into using the conditional split transformation to perform your logic checks. Use a derived column transformation to add a column.|||

sureshv wrote:

1.Add text file source

What i want to achieve here is if the text file countains the column name in the first row i should delete them and if it does not contain column name in the first row just transfer it.

how can this be achieved?

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

sureshv wrote:

2.add one more column to my text file which should contain the status(insert or update).

how can this be done?

Use a derived column to add extra columns to data flow pipeline

sureshv wrote:

3.before transfering data ot destination i want to know if the record exists if exists i just want to update it instead of insert.and if new record i want to insert it .and the status in the above new column need to change.

There are tons of threads discussing that issue; the most popular is to use Lookup transform against the destination table; if a match occurs means the row already exists; otherwise it does not.

|||

In the first case.

Data is just a text file with column names in the first row.

eg:

name address telno

xxx yyy zzz

xxx sdfdsf sdfsf

But i dont want the column names coming from text file. instead i want to have my own column names(which i can and know to create)

If i transter data as in the text file the column names would appear in the first row.just to get rid of it i want to remove column name from text file.

In second case:

I use derived column to add a column,and conditional split for bad data.

My point is in the new column how should i insert the status(insert) or )update.

what should i use.

|||

There is a property on the flat file connection manager that you can configure for that (ColumnsNamesInFirstDatarow).

I dont want to use this i can have and want to have my own column names.WHich can be done and i know about it.

please look at my reply earlier

|||

sureshv wrote:

My point is in the new column how should i insert the status(insert) or )update.

see if this helps:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

you could add a derived column transform at after each lookup transform and add there a column with the expected value(insert/update)...

dataflow error

This error seem to be very silly.did anyone come across this error.

I have been transferring data from textfile to a table using oledb destination.

The number of records in the text file are 2,091,650

Its was running just fine couple of days ago when the incoming data was little small then this...(arround 300,000).Now it seem to have a problem.

Here is the flow

1.File System task ->I copy the file to different location

2.Execute sql task->truncate tables

3.DataFlow task->I check for only the error files in this data flow.and all valid rows i transfer to a different text file.

4.Dataflow->filesource i connect to new text file created earlier.Here i convert fields to repective datatype and i insert if new or update record.

I dont know whats going on...

When i run my package it runs through the first three perfectly fine.When it comes to fourth step it sits there.....it dosent go to the tasks within this dataflow at all...and begining i have flat files source...

What could be the reason...

when i look at progress tab...i was able to look at the progress of other tasks but when it comes to this task it shows start>>>>>time and it sits there...

Are you using the same file connection manager for the "new" file created in step 3 and used in step 4? If so, try creating a second file connection manager for the file and use that connection manager in step 4.|||Do you have step three hooked up to step four via a precedence constraint?|||

I checked it out using a same file connection and different connection manager as well...but it didnt work...seems to really silly problem....

And all my flows are perfectly connected with precedence constraint as well.

Then only change which was made to this package was increase in volumn of data.

How can i fix this?Is there any other solution?

I also checked executing the last dataflow task alone as the file had data in it.

But it sits there and dosent proceed.

|||

Suresh,

could you troubleshoot this by making a simple package to only process your flat file? If it fails, try cutting the file in pieces to find out if it has some formatting errors.

HTH.