February 21, 2008 at 2:27 pm
i have a text file so i have jus selected source as flatfile and the data in text is in this format
600602824CBKRECONPAPERRE0189767603000000173902-JUL-0315976.99Anda, Inc./WestonWestonFLa
600602824CBKRECONPAPERRE0189767603000000173902-JUL-0315976.99Anda, Inc./WestonWestonFLa
600602824CBKRECONPAPERRE0189767603000000173902-JUL-0315976.99Anda, Inc./WestonWestonFLb
600602824CBKRECONPAPERRE0189767603000000173902-JUL-0315976.99Anda, Inc./WestonWestonFLd
i have selected delimited format with header row delimiter as tab ...what should i give for text qualifier...
and i have other problem too...i should append this to an already existing table which have two additional columns
1)identity column ---so how can i append this rows to that table with identity function for ex if that table already has 30 rows now this new row should start from 31 as identity no and should continue.....
2) the other thing is that i need to insert other column with a default value of 4 for all my rows i m importing now ..i cant add in my source as i have thousand of rows....
the already existing table has the format in this way
Id SrcFileId ST_NUM SUB_NUM NOTI_ID COM_ID B_DEST S_NUM SUNO
1 1 600017 60 CBKRECON PAPER RE0189767 18 00174594
2 1 600017 60 CBKRECON PAPER RE0189767 18 00143392
1292 1 600896 832 CBKRECON PAPER RE0189767 897 0000434360
so now the new rows i imported should start from 1293 and the i shpuld insert a field src with 4 in that for all of my fields how can i do that in import wizard in sql management studio ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 21, 2008 at 7:51 pm
Text qualifier are used to explicitly delimit the data in a column where it is possible for the column to contain the character used as the field delimiter.
e.g. you say you are using tab as the delimiter. If it is possible for a column to contain a tab character, you would need to use a text qualifier.
Looking at your sample data, you have no text qualifiers. I also suspect that you won't need them since it is not common to have a tab character within a field.
To import into the existing table that has the extra columns, I would use a view that matches the columns that are present in your flat file.
HOWEVER, you may need to think about what would happen if the SSIS package fails but imports some (but not all) of the data.
You need to deal with this otherwise you may end up importing some records twice. This requires a primary key so that you can work out whether the record already exists and some conditional logic to manage the import of existing or new records.
February 21, 2008 at 7:59 pm
i m new to sql server so i dnt know abt that could plz let me know how to use views in that case and the primary key here is identity value which i should add to the new rows imported
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply