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).
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
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
|||
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)...
