my file is like this TH*4.2*857463*01**20091015*1045*P**~~IS*7564*ORACLE~ i have two table table : TH ID TH1 TH2 TH3 TH4 TH5 TH6 TH7 TH8 TH9 TH 4.2 857463 01 * 2009105 1045 p * ~~ TABLE : IS ID IS01 IS02 IS 7564 oracle now in my flat file connection manager i am using column delimeter as *,but then i cannot store values in TH04 and th08 it just skip *, how do i store value i shown here

Use "Text Qualifier" values to add Quotations around the value i.e. put " in the "Text Qualifier" textbox of Flat file connection. Please mark the post as answered if it answers your question

i would suggest take it in fixed width instead. everything in column 0 then out a derived colum and derive columns with substring ID = SUBSTRING(column,1,1) TH1 =SUBSTRING(COLUMN,2,1).... so on Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

even if i put '' in tex qualifier mthe result is same,i am getting th4 values blank

even if i put '' in tex qualifier mthe result is same,i am getting th4 values blank There is something that you are not doing right. Besides you sample data is also not correct i.e. your sample data is: TH*4.2*857463*01**20091015*1045*P**~~IS*7564*ORACLE~ where the correct data according to your problem description should have been (i.e. an estric is missing as a value): TH*4.2*857463*01***20091015*1045*P***~~IS*7564*ORACLE~ due to this missing * you are having an empty field value is going into your database.Please mark the post as answered if it answers your question

even if i put '' in tex qualifier mthe result is same,i am getting th4 values blank There is something that you are not doing right. Besides you sample data is also not correct i.e. your sample data is: TH*4.2*857463*01**20091015*1045*P**~~IS*7564*ORACLE~ where the correct data according to your problem description should have been (i.e. an estric is missing as a value): TH*4.2*857463*01***20091015*1045*P***~~IS*7564*ORACLE~ due to this missing * you are having an empty field value is going into your database. Please mark the post as answered if it answers your question but still you would need to have a text qualifier or else your package will not work right as it would assume the * as a field separator NOT a field value.Please mark the post as answered if it answers your question

Your data should be like this TH*4.2*857463*01*"*"*20091015*1045*P*"*"*~~IS*7564*ORACLE~ and Text qualifier Must be "(one double quote). Else change the column delimiter to , or | or which ever yo don't expect in the file as a value.Rajkumar

when you take in your flat file, in flat file source, in columns tabs see if you have column delimter \... try that Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

ok this is my file TH*4.0*347*01**20120514*091509*P*:\IS*8007487001*HEALTH\ now here column delimeter is * and TH*4.0*347*01**20120514*091509*P*: i need to seperate this field and putinto table so my field would be id th0 th1 th2 th3 th4 th5 th6 TH 4.0 347 01 * 20120514 091509 P and i need to seperate IS*8007487001*HEALTH and put it into file liek this id is0 is1 IS 80007487001 health please help

th3 valye is *..? also does every time the length of this whole string would be same TH*4.0*347*01**20120514*091509*P*:\IS*8007487001*HEALTH\ or it can be TH*4.00*347.56*01.11**20120514--89*091509666*P*:\IS*8007487001555*HEALTH\HEALTHSPlease mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

yes if they dont provide anythign that will be come as *, so th3 will be *, the length will be same

Are you sure about the tha3? it seems that it would be NULL. However it is easy to make it * if that is the case! :) But still you didn't say how is your text file. Is it a long string of characters without <CTR><LF> as record separator? can you cut and paste like 6 records without any change? That will give us a clearer picture about you flat file

this is my file TH*4.0*347*01**20120514*091509*P*:\IS*8007487001*Health.com \PHA*1619252160*1832674*FH1427536*HealthWarehous*7107 Industrial**Flore*KY*41042*8007487001\PAT*******LOAR*DOUGLAS****132 Out**Berkeley Springs*WV*25411*3048398729*19500629*M*01*99\ now this is one file,i will have multiple file in folder that will come on every hour . so i need to know where i need to start. as you see in this file starts from TH ans end to \ i need to store this data in 1 table, from IS to \ i need to store in second table. if any field is missing they are provide * , also they are using * as a column delimiter. i need to know from where should i start,for example, if any file comes in the folder, i should have some event and i need to go through that file and put their data into table. once its data is in table,the file should be deleted from there. any help

Hi tsql_new, You can use WMI Event Watcher Task to Wait for notification that files have been added to a folder and then initiate the processing of the file. Then use Foreach Loop Container loops through these files and process this files use Script Component. Please see: http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html And for more information about parsing Non-Standard Text File using Script Component, you can refer to: http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html http://msdn.microsoft.com/en-us/library/ms345160.aspx Thanks, EileenPlease remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.