Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql