How to increase SSIS performance

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Tom De Cort wrote:

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Speeding up by replacing the script task doesnt surprise me. If you have a high-performant database engine at your disposal, why not use it!

Some links for you that may help:

Whitepaper on Performance Tuning Techniques
http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

Donald Farmer's Technet webcast (highly recommended)
http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx

-Jamie

|||

Hi Jamie,

Great, I've read the whitepaper and discovered a lot!

Greets,
-Tom

How to increase SSIS performance

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Tom De Cort wrote:

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Speeding up by replacing the script task doesnt surprise me. If you have a high-performant database engine at your disposal, why not use it!

Some links for you that may help:

Whitepaper on Performance Tuning Techniques
http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

Donald Farmer's Technet webcast (highly recommended)
http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx

-Jamie

|||

Hi Jamie,

Great, I've read the whitepaper and discovered a lot!

Greets,
-Tom

How to import Data from Excel to SQL Server Express

Hi,
please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .

Your query is not clear.. you wish to copy data from Excel to SQL Server Express or You wish to read Excel data from ASP.Net application:

If you want to know, how to read Excel data in ASP.Net Application, visit the follocing web link:

http://weblogs.asp.net/scottgu/archive/2006/05/29/Reading_2F00_Writing-Excel-Spreadsheets-with-ADO.NET.aspx

|||

Hi,

You may try this SQL fonction OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

INSERT INTO MyTable

SELECT Column1, Column2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=C:\MyTable.xls',
 'SELECT * FROM [Sheet1$]')

The first row of your Excel file should coutains the colums names.

 Hope this answer your question. 

Dominic 

|||

hi, exactly i would like to copy two columns of data from Excel file instead of two columns of data in sql express,thank u

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based on
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
DirkAt previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.|||Hi Thomas,
thanks for your reply. I'm sure this approch works but I would like to make
it easier. With your solution I have to join the tables every time I need a
lagnuage relevant value.
I was thinking about using the Sql Server 2005 FieldType 'xml' and store a
xml strcture in every relevant field like
<values>
<value culture='en-us'>
car
</value>
<value culture='de-de'>
Auto
</value>
</values>
I just want to hear some solution from others just to make it no so
complicated...
Thanx,
Dirk

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based o
n
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
DirkAt previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.

How to implement a data base design for multi language support?

Hi,
I have searched the web for several hours now to find a reference solution
based on the available features of Sql Server 2005 to implement a data base
with multi language support.
I would like to create a database that is able to store multiple
translations (text in different languages) for the same original value.
--> tblProducts, Field ProductDescription --> One field, manny languages
If I query this table/field with a current UI-Culture from
ASP.net/WindowsForms it should be 'intelligent' and return the value based on
the UI-Culture. It should work like the culture stuff in the GUI...
I am lookig for a solution, where I can store all the language relevant
informations in the same field. Do I have to use an XML-field or a user
defined field? Is there something like a "best practise" for this kind of
database design available?
I would like to avoid a 1 to many relation for all culture relevant fields
in my database...
Thanks for any hint,
Dirk
At previous company i worked at they had multi language support and
they used what was like a codes table. w/ a language type ID
associated w/ all the words on the various pages. depending on the
language type code it would pull in the words to the page. so they
just stored 10 differenet sets of language codes in the db. they
brought it in fresh everytime w/ a select statement.