Hi jamie, The source data which comes doesn't have column names for all the text files i can't open text files and write column names manually because each text file size is very big(2 GB). that's the reason why i am loading all 250 columns in to staging table with 250 columns and then i will load the main columns which i need into main table. that's why i need to load data twice is there any way that i can insert columns names in the source data and map it directly to main table. Kind reagrds Ravilla

HI Koen, i have used it. Still it is very slow. Kind regards Ravilla

Hi jamie, The source data which comes doesn't have column names for all the text files i can't open text files and write column names manually That's not what I was suggesting you do. because each text file size is very big(2 GB). that's the reason why i am loading all 250 columns in to staging table with 250 columns and then i will load the main columns which i need into main table. that's why i need to load data twice is there any way that i can insert columns names in the source data and map it directly to main table. Kind reagrds Ravilla You seem to be under the impression that your source files need to have column names in them. That is not the case. You also seem to be under the impression (correct me if I'm wrong) that all columns in the file need to be laoded into the destination. That is not true, if you don't need a column in the destination then simply ignore it.ObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

What is slow? How much time does it take to insert 2 million rows? What options did you check in the OLE DB Destination? Does the destination table have a lot of indexes? What is the recovery model of the database? Do you have any transformation in the package?MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, OLE DB Destination, fast load option is the best but we have another one i.e., BulkInsert task. This Bulkinsert Task is very fast when compare to others, but i dont prefer this one becoz it loads some junk in some times. Thanks, Munna

With the paucity of information that you have provided it is impossible to offer any really good advice. Why are you concluding that its slow? How long is it taking?How long do you expect it to take?How long do you *need* it to take?What is the nature of your dataflow (i.e. what components are being used, how have you configured them)? How wide is each row in the source?How wide is each row in the destination?Are the two million records spread over many files or just one file?Are all of the columns in the source file(s) being loaded into the destination? Help us to help you. ObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

HI jamie, Ya Thanx for your help. kind regards ravills

HI Koen, I have to load the multiples text files into sql server. each text file has 250 columns in it but in my sql server i just need to select 142 from them . i have designed my package like this: 1. Used for each loop container to read mutiple text files. 2. inside for each loop i have used data flow task. 3. i have created a staging table which loads all 250 into my staging table. 4. out side the for each loop i have used execute SQL TASK and i have written T_sql to select the required 142 coulmns and load in to main table. Hope this information might be helpfull for you to help me. Kind reagrds Ravilla

HI Jamie, I have to load the multiples text files into sql server. each text file has 250 columns in it but in my sql server i just need to select 142 from them . i have designed my package like this: 1. Used for each loop container to read mutiple text files. 2. inside for each loop i have used data flow task. 3. i have created a staging table which loads all 250 into my staging table. 4. out side the for each loop i have used execute SQL TASK and i have written T_sql to select the required 142 coulmns and load in to main table. 5. it is taking almost 1 day to laod. is there any where i can increase the speed. Hope this information might be helpfull for you to help me. Kind reagrds Ravilla

HI jamie, Ya Thanx for your help. kind regards ravills How is this the correct answer? When people stumble on this post through a search engine, the first thing they see is "Ya Thanx for your help." That shall really solve their issue...MCTS, MCITP - Please mark posts as answered where appropriate.

Why don't you read all the flat files at once, put everything together with the union all and write it directly to the destination table?MCTS, MCITP - Please mark posts as answered where appropriate.

Why don't you read all the flat files at once, put everything together with the union all and write it directly to the destination table? Good idea. or you could use the multiflatfile adapter. I compare your various options here: SSIS: Processing data from multiple files all at onceObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

HI Jamie, I have to load the multiples text files into sql server. each text file has 250 columns in it but in my sql server i just need to select 142 from them . i have designed my package like this: 1. Used for each loop container to read mutiple text files. 2. inside for each loop i have used data flow task. 3. i have created a staging table which loads all 250 into my staging table. 4. out side the for each loop i have used execute SQL TASK and i have written T_sql to select the required 142 coulmns and load in to main table. 5. it is taking almost 1 day to laod. is there any where i can increase the speed. Hope this information might be helpfull for you to help me. Kind reagrds Ravilla Yes, that's helpful. My first observation is that you are doing 250 insertions into your staging table; moreover you are inserting all the data twice, once into your staging table and once into your final destination table. All of that is bad bad bad. See the link I shared with you previously to explore options for making this more efficient. ObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

I have to load the multiples text files into sql server. each text file has 250 columns in it but in my sql server i just need to select 142 from them . Parsing those additional 108 columns is work that is totally superfluous, having said that it does not mean that there is a quicker method. One thing you might want to try is to import each row into your dataflow as a single-column row (which will be MUCH much quicker than parsing 250 columns in the Flat File Source Adapter) and then parse out the 142 required columns using a Derived Column component. Parsing in the Derived Column component will be slowwww so its up to you to determine whether the trade-off between parsing 250 columns in the Flat File Source Adapter or parsing 142 columns in the Derived Column component is worth it. Test and measure, test and measure, test and measure...ObjectStorageHelper<T> A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi, I am trying to load 2 million falt files source data in to sql server using SSIS. I have loaded the data but it is taking to long to load the data in to sql server, can anyone suggest me what is the quickest way to load 2million records in to sql server using SSIS. Thanks Kind reagrds Ravilla.

In the OLE DB Destination, make sure you use the fast load option.MCTS, MCITP - Please mark posts as answered where appropriate.