SSIS Performace Issue

Question

SSIS Performace Issue

koti.raavi

SSCrazy

Points: 2756
More actions
August 8, 2016 at 4:35 am

#328925

[font="Verdana"]
I'm loading the couple of text files to SQL Server Database and
each file contains 1000 records and total number of files are 15.
Problem is each file is taking more then 5mins time to load.
File contains 12 columns (String)
Converting 2 columns into integer by using derived column and remaining 10 are loading as string
Below is the code used for conversion
(DT_I4)Mailer_ID
(DT_I4)Move_Effective_Date
I have tried to increase DFT buffer size and data conversion is used in place derived column. But problem still exist
Thanks,
Dan
[/font]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

koti.raavi (8/8/2016)
I'm loading the couple of text files to SQL Server Database and
each file contains 1000 records and total number of files are 15.
Problem is each file is taking more then 5mins time to load.
File contains 12 columns (String)
Converting 2 columns into integer by using derived column and remaining 10 are loading as string
Below is the code used for conversion
(DT_I4)Mailer_ID
(DT_I4)Move_Effective_Date
I have tried to increase DFT buffer size and data conversion is used in place derived column. But problem still exist
Thanks,
Dan

So ... is it a couple, or is it 15 files?

What does the target table look like ... how many rows does it contain?

Can you post the DDL, including index definitions, for the target table?

For such a small number of rows, there should be no need to play around with buffer sizes.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

Yes 15 files, Its newly created and doesn't have any indexes at all.Thanks

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

OK. Does each file take the same length of time?

Are the files stored locally on the server?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

koti.raavi SSCrazy Points: 2756 More actions · Answer 4

Depends upon data.. One file got 500 rows took 4mins and other file 1000 rows took 7mins..

Yes loading in local temp server.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

So, to confirm, the target table is a heap – is that correct? Or does it have a primary key?

Are you loading in series or in parallel?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

koti.raavi SSCrazy Points: 2756 More actions · Answer 6

Yes ..heap table and series execution..havent tried for parllel execution

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

koti.raavi (8/8/2016)
Yes ..heap table and series execution..havent tried for parllel execution

Then I am out of ideas right now.

Can you try this on another machine ... perhaps on a local instance ... to see whether it happens there too? Just to attempt to rule out any possible environmental issues.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

RonKyle SSC-Dedicated Points: 31560 More actions · Answer 8

I don't know this will help your speed issue, but consider not doing the conversion between the file and the landing table. Your first job is to just get the data. Once you have it, you can start converting and cleaning from there.

koti.raavi SSCrazy Points: 2756 More actions · Answer 9

Thanks Phil, I will check in different server and will see how it goes .

koti.raavi SSCrazy Points: 2756 More actions · Answer 10

I will try converting the data after loading. One more question , string data what we have in text file having some spaces.What is the best way to do?. Can we do in package level by using derived column or creating sp and including sp in package .Thanks

Phil Parkin SSC Guru Points: 246993 More actions · Answer 11

koti.raavi (8/8/2016)
I will try converting the data after loading. One more question , string data what we have in text file having some spaces.What is the best way to do?. Can we do in package level by using derived column or creating sp and including sp in package .Thanks

String data often has spaces in it, and SSIS can import such data. So ... I'm not sure what the problem is. What exactly are you trying to do?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel