multiple rows insert: performance

Showing posts with label performance. Show all posts

Friday, March 30, 2012

How to evaluate the performance of the sql server

Hi all,
How to evaluate the performance of the SQL server 2000?
Hope you can help.
EricIt depend of what area of performance that you are looking for. If you
are looking for database access or query performance, you can use
Profiler or examine the query execution plan.
If you want to see the server performance, you can use performance counter.
Eric wrote:
> Hi all,
> How to evaluate the performance of the SQL server 2000?
> Hope you can help.
> Eric|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:24381665-3E5C-4554-84D7-B798402D8FF6@.microsoft.com...
> Hi all,
> How to evaluate the performance of the SQL server 2000?
> Hope you can help.
> Eric
Brad McGehee put together a great article on this.
http://www.devarticles.com/c/a/SQL-...-
Audit/
Rick Sawtell
MCT, MCSD, MCDBA

How to evaluate the performance of the sql server

Wednesday, March 28, 2012

How to evaluate the performance of the sql server

Hi all,
How to evaluate the performance of the SQL server 2000?
Hope you can help.
Eric
It depend of what area of performance that you are looking for. If you
are looking for database access or query performance, you can use
Profiler or examine the query execution plan.
If you want to see the server performance, you can use performance counter.
Eric wrote:
> Hi all,
> How to evaluate the performance of the SQL server 2000?
> Hope you can help.
> Eric
|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:24381665-3E5C-4554-84D7-B798402D8FF6@.microsoft.com...
> Hi all,
> How to evaluate the performance of the SQL server 2000?
> Hope you can help.
> Eric
Brad McGehee put together a great article on this.
http://www.devarticles.com/c/a/SQL-S...ormance-Audit/
Rick Sawtell
MCT, MCSD, MCDBA

How to enhance the performance when there is bulk update in trigge

Dear someone who can help,
I encounter a problem where the execution of a trigger is very slow (in
terms of minute). Below is the example of the trigger that I have been
implemented.
Assume the trigger is fired on Table1 when inserting. The trigger perform
the following
- Create a temporary table of @.Temp
- Retrieve all the records from Table2 and insert into a temporary table.
- Then loop each of the temporary-Record and perform some business logic
- Update the record back into Table2
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.InsCode > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Question:
1. Is the temporary table method correctly been used? I understand that
Cursor is not recommended in Trigger.
2. How to speed up the performance.
3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
to MS Sql.
4. I have tried with call a stored procedure with explicit
transaction, but it didn't help much. Moreover, sometime this method only
able to update few rows of record in Table2 after the trigger execution.
Thank you in advance.
regards,
StephanieStephanie wrote:
> Dear someone who can help,
> I encounter a problem where the execution of a trigger is very slow
> (in terms of minute). Below is the example of the trigger that I have
> been implemented.
> Assume the trigger is fired on Table1 when inserting. The trigger
> perform the following
> - Create a temporary table of @.Temp
> - Retrieve all the records from Table2 and insert into a temporary
> table.
> - Then loop each of the temporary-Record and perform some business
> logic
> - Update the record back into Table2
> Question:
> 1. Is the temporary table method correctly been used? I understand
> that Cursor is not recommended in Trigger.
> 2. How to speed up the performance.
> 3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second)
> compare to MS Sql.
> 4. I have tried with call a stored procedure with explicit
> transaction, but it didn't help much. Moreover, sometime this method
> only able to update few rows of record in Table2 after the trigger
> execution.
> Thank you in advance.
> regards,
> Stephanie
It's considered bad practive to use cursors and any extensive processing
in triggers as they will slow down the entire transaction, keeping locks
active in the database much longer than needed. Sometimes, if the
business rules require it, you can get away with it without affecting
performance too much.
What I don't understand is why there is no reference to reference to the
inserted virtual table in the trigger. What you have is a trigger that
affects all rows in another table every time a row is updated. I think
you really need to explain to the group what you are trying to do in the
trigger and why it is necessary.
David Gugick
Imceda Software
www.imceda.com|||Dear David,
In this example, i'm trying to show that when i update record in Table1, i
need to update the 'Code' field in Table2, in this case, will update the in
bulk. May be i should correct my example as below:
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2 where
Flag = 'Y'
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.Code > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Hope this is clear. Thank you.
regards,
Stephanie|||Don't use cursors or loops in a trigger. Typically it isn't a good idea
to assign column values to variables in a trigger as this normally
implies you'll have to use a loop if there is more than one row
updated.
You can handle multiple updated rows by referring the INSERTED and
DELETED tables in your trigger code. Example:
UPDATE SomeTable
SET something ...
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE Inserted.key_col = SomeTable.key_col)
Unfortunately your trigger code doesn't do anything useful so it isn't
really possible to give a solution specifc to your situation.
David Portas
SQL Server MVP
--|||On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
(snip)
>Question:
>1. Is the temporary table method correctly been used? I understand that
>Cursor is not recommended in Trigger.
Hi Stephanie,
This is still a cursor. Everytime you loop through a set of rows and
process one row as a time, you're performing a cursor operation, even if
you are not using the prebuilt cursor tools for it.
Warning against using cursors should be taken as warning against any
form of iterative processing. In SQL, you should write queries that
operate on whole sets at once.
Another thing (even though you don't ask) - a trigger fires once per
statement execution. All rows affected by the statement (be it one, zero
or five million) are in the inserted pseudo-table. The code you posted
will result in an error if there are more than one (but if you used
SELECT instead of SET, it would not error - it would just pick one of
the rows and process only that one). And it will produce wrong results
if no rows are affected (as @.InsCode will be set to NULL).

>2. How to speed up the performance.
Remove the cursor. Replace it with a set-based UPDATE statement.
You've posted two slighhtly different versions of your trigger, but I
believe that your real trigger is more complicated than this (if only
because the CREATE TRIGGER statement itself would cause an error). As an
exampl, I'll post a set-based equivalent of the second trigger you
posted:
CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
AS
UPDATE Table2
SET Code = (SELECT i.InsCode
FROM inserted AS i)
* CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
go
As you can see, the set-based code is much shorter. If you test it,
you'll find that it's much faster as well.
In case you need help rewriting your real code in set-based form, you'll
need to post the table structure (as CREATE TABLE statements), some
sample data (as INSERT statements) and expected output. Posting the slow
code you currently use might help as well. See www.aspfaq.com/5006.

>3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
>to MS Sql.
I don't know Oracle myself, but based on what I've heard, it appears as
if SQL Server is heavily optimized for set-based processing (with the
obvious tradeoff in cursor operations), whereas Oracle is either more
optimized for cursorbased operation, or a bit for both. In any case,
cursors do tend to be faster in Oracle.

>4. I have tried with call a stored procedure with explicit
>transaction, but it didn't help much. Moreover, sometime this method only
>able to update few rows of record in Table2 after the trigger execution.
Do you mean that some rows in Table2 are changed and some are not? If
the rows that need to be changed are determined by the data in the
inserted pseudo-table, then this makes sense (as only one of the rows in
inserted will be processed). But with the code you posted, this should
not be possible - it should either process all rows in table2, or none
at all - regardless of transactions in the calling stored procedure (a
trigger is always part of a transaction - if not explicit, then implicit
as part of the statement that fires the trigger). If you can post some
code to reproduce this (I'd need at least the CREATE TABLE and INSERT
statements to get the starting data, the actual trigger code and the
statement that fires the trigger), I'll gladly look into it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

How to enhance the performance when there is bulk update in tr

Dear Hugo,
Thanks for your solution. With the set-based UPDATE method, really find that
it's much faster.
I’ll have another question. Lets take (my) the same example, now I need to
add another update trigger in Table2 as below (where the insert trigger in
Table1 still remain). In this trigger, what ever record(s) updated in Table2
will insert a new record into Table3.
========================================
=============
CREATE TRIGGER TG_Table2_Upd ON Table2
DECLARE @.Msg varchar(5) /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3))
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr) Select KeyStr From inserted
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Msg = ‘U’ + @.KeyStr
Insert into Table3 (UpdateTime, Message) values (CURRENT_TIMESTAMP, @.Msg)
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
=============
In this case, how can I improve the performance?
regards,
Stephanie
"Hugo Kornelis" wrote:

> On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
> (snip)
> Hi Stephanie,
> This is still a cursor. Everytime you loop through a set of rows and
> process one row as a time, you're performing a cursor operation, even if
> you are not using the prebuilt cursor tools for it.
> Warning against using cursors should be taken as warning against any
> form of iterative processing. In SQL, you should write queries that
> operate on whole sets at once.
> Another thing (even though you don't ask) - a trigger fires once per
> statement execution. All rows affected by the statement (be it one, zero
> or five million) are in the inserted pseudo-table. The code you posted
> will result in an error if there are more than one (but if you used
> SELECT instead of SET, it would not error - it would just pick one of
> the rows and process only that one). And it will produce wrong results
> if no rows are affected (as @.InsCode will be set to NULL).
>
> Remove the cursor. Replace it with a set-based UPDATE statement.
> You've posted two slighhtly different versions of your trigger, but I
> believe that your real trigger is more complicated than this (if only
> because the CREATE TRIGGER statement itself would cause an error). As an
> exampl, I'll post a set-based equivalent of the second trigger you
> posted:
> CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
> AS
> UPDATE Table2
> SET Code = (SELECT i.InsCode
> FROM inserted AS i)
> * CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
> go
> As you can see, the set-based code is much shorter. If you test it,
> you'll find that it's much faster as well.
> In case you need help rewriting your real code in set-based form, you'll
> need to post the table structure (as CREATE TABLE statements), some
> sample data (as INSERT statements) and expected output. Posting the slow
> code you currently use might help as well. See www.aspfaq.com/5006.
>
> I don't know Oracle myself, but based on what I've heard, it appears as
> if SQL Server is heavily optimized for set-based processing (with the
> obvious tradeoff in cursor operations), whereas Oracle is either more
> optimized for cursorbased operation, or a bit for both. In any case,
> cursors do tend to be faster in Oracle.
>
> Do you mean that some rows in Table2 are changed and some are not? If
> the rows that need to be changed are determined by the data in the
> inserted pseudo-table, then this makes sense (as only one of the rows in
> inserted will be processed). But with the code you posted, this should
> not be possible - it should either process all rows in table2, or none
> at all - regardless of transactions in the calling stored procedure (a
> trigger is always part of a transaction - if not explicit, then implicit
> as part of the statement that fires the trigger). If you can post some
> code to reproduce this (I'd need at least the CREATE TABLE and INSERT
> statements to get the starting data, the actual trigger code and the
> statement that fires the trigger), I'll gladly look into it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:

>Dear Hugo,
>Thanks for your solution. With the set-based UPDATE method, really find tha
t
>it's much faster.
>Ill have another question. Lets take (my) the same example, now I need to
>add another update trigger in Table2 as below (where the insert trigger in
>Table1 still remain). In this trigger, what ever record(s) updated in Table
2
>will insert a new record into Table3.
(snip)
Hi Stephanie,
The trigger code you posted will never work. You have omitted a part of
the CREATE TRIGGER statement, and you have an unmatched END.
Anyway, instead of filling a temp table, iterating iver the rows and
inserting new rows one at a time in Table3, you can easily do this in
one statement:
CREATE TRIGGER TG_Table2_Upd
ON Table2
AFTER INSERT, UPDATE -- Wild guess
AS
INSERT INTO Table3 (UpdateTime, Message)
SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
FROM inserted
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much! :)
regards,
Stephanie Kan
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>
> (snip)
> Hi Stephanie,
> The trigger code you posted will never work. You have omitted a part of
> the CREATE TRIGGER statement, and you have an unmatched END.
> Anyway, instead of filling a temp table, iterating iver the rows and
> inserting new rows one at a time in Table3, you can easily do this in
> one statement:
> CREATE TRIGGER TG_Table2_Upd
> ON Table2
> AFTER INSERT, UPDATE -- Wild guess
> AS
> INSERT INTO Table3 (UpdateTime, Message)
> SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
> FROM inserted
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

How to enhance the performance of a table

hi i have table with around 15 fields and has a pk.
there're some web applications will insert records into it. there're some
backgroup application will query the table.
now, i have around 1000 thousand records, but the there're some locking
behaviour, and even i go query analyzer and do simple query search, i have
the timeout error.
how can i simply improve the performance of it?
thanks!
mullinHi,
how can i simply improve the performance of it?
Create Indexes based on the Where clause of your query. This will definetely
speed up your queries.
How to reduce locks:
1. Create necessary indexes
2. Make the transaction as short as possible.
3. Might be I/O bottle neck.
Whenever your server experiences an I/O bottleneck, the longer it takes
user's transactions to complete.
And the longer they take to complete, the longer locks must be held, which
can lead to other transactions
having to wait for previous locks to be released.
Thanks
Hari
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uqbTi72JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> hi i have table with around 15 fields and has a pk.
> there're some web applications will insert records into it. there're some
> backgroup application will query the table.
> now, i have around 1000 thousand records, but the there're some locking
> behaviour, and even i go query analyzer and do simple query search, i have
> the timeout error.
> how can i simply improve the performance of it?
> thanks!
> mullin
>

How to enhance the performance of a table

hi i have table with around 15 fields and has a pk.
there're some web applications will insert records into it. there're some
backgroup application will query the table.
now, i have around 1000 thousand records, but the there're some locking
behaviour, and even i go query analyzer and do simple query search, i have
the timeout error.
how can i simply improve the performance of it?
thanks!
mullin
Hi,
how can i simply improve the performance of it?
Create Indexes based on the Where clause of your query. This will definetely
speed up your queries.
How to reduce locks:
1. Create necessary indexes
2. Make the transaction as short as possible.
3. Might be I/O bottle neck.
Whenever your server experiences an I/O bottleneck, the longer it takes
user's transactions to complete.
And the longer they take to complete, the longer locks must be held, which
can lead to other transactions
having to wait for previous locks to be released.
Thanks
Hari
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:uqbTi72JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> hi i have table with around 15 fields and has a pk.
> there're some web applications will insert records into it. there're some
> backgroup application will query the table.
> now, i have around 1000 thousand records, but the there're some locking
> behaviour, and even i go query analyzer and do simple query search, i have
> the timeout error.
> how can i simply improve the performance of it?
> thanks!
> mullin
>

Wednesday, March 21, 2012

How to effectively troubleshoot?

I am a developer but I've been put in a position to have to troubleshoot
performance problems on SQL Server 2000 and 2005. So when users
complain that the "system is slow", I do various things, like run
"sp_who2 active" and looking for processes blocking each other.
However, if I don't find anything, I am not sure what to do next.
So I have a couple of questions:
1. I look at Management/Activity Monitor/Process Info window. Is the
Physical IO column cumulative? Or does it reflect the IO in the current
transaction?
2. I've noticed that the tempdb database is on the same drive as the
main database's log file. The box has 4 drives in a RAID5
configuration. Is this normal? Can it be impacting performance? If,
so, what should be the optimal configuration given the 4 available
drives in a RAID5 config?
3. Can someone recommend a good troubleshooting guide for both SQL 2000
and 2005?Do yourself a favor if possible. Hire a professional to give you a
performance review - and mentor you at the same time! Win-Win for you.
A good tuner uses a mix of training, experience, art (and sometimes luck).
It is too wide and deep a topic to promulgate via a newsgroup. :-)
Answering your questions specifically
1) I believe I/O shown is cumulative for that spid's existence, which could
include much more work than just the ongoing 'current transaction'.
2) tempdb is best placed on a separate spindle from other dbs. Raid 5 is
not optimal for it, or for (especially) log files. It doesn't seem like you
have any flexibility if all you have is 4 drives in raid5 however.
3) Microsoft and other entities have training classes you can take on perf
tuning. I don't know of a good 'beginner's guide to tuning' though. There
are some very good ones for experienced people, but I think they may well
confuse/confound you more than help.
TheSQLGuru
President
Indicium Resources, Inc.
"Frank Rizzo" <none@.none.com> wrote in message
news:%233Fw47FqHHA.3892@.TK2MSFTNGP05.phx.gbl...
>I am a developer but I've been put in a position to have to troubleshoot
>performance problems on SQL Server 2000 and 2005. So when users complain
>that the "system is slow", I do various things, like run "sp_who2 active"
>and looking for processes blocking each other. However, if I don't find
>anything, I am not sure what to do next.
> So I have a couple of questions:
> 1. I look at Management/Activity Monitor/Process Info window. Is the
> Physical IO column cumulative? Or does it reflect the IO in the current
> transaction?
> 2. I've noticed that the tempdb database is on the same drive as the main
> database's log file. The box has 4 drives in a RAID5 configuration. Is
> this normal? Can it be impacting performance? If, so, what should be the
> optimal configuration given the 4 available drives in a RAID5 config?
> 3. Can someone recommend a good troubleshooting guide for both SQL 2000
> and 2005?
>|||Hello Frank,
Usually you shall identity the bottleneck of server such as memory, IO or
CPU. Also, you may way want to identify and optimize slow runing queries
that often run on your server. the following articles might be a start
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
10 Baselining Tips for SQL Server:
http://www.sql-server-performance.com/gv_baselining_tips.asp
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
If you suspect slow running queries to be causing the performance problem,
please refer to the following article:
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
822101 The waittype and lastwaittype columns in the sysprocesses table in
SQL
http://support.microsoft.com/?id=822101
Hope this is helpful. Thank you.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 7, 2012

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Secondly, if there are any changes in values in the records 1st to 1000th record, how to I compare and only update the value that has changed? Is there any particular configuration setting in sql that I can use?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page

discusses just some of the problems you will run into with the Lookup

component. Let me know what you think!!!

Thanks,
Greg Van Mullem|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

I'm slightly sceptical as to why this is actually necassary though. Your justification for doing it all in code seems to be that using lookups "needlessly fills up the destination databases transaction log with hoards of update commands" and "It prevents counting the records that actually needed to be updated." Well did you explore using LOOKUPs to find out whether a row that already exists has actually changed or not? Or even a derived column/conditional split component subsequent to your LOOKUp that compares the values in the pipeline with the values in the LOOKUP dataset? That is eminently possible and will solve the two problems that you mention here.

Great work though.

-Jamie

|||

Jamie,

Thanks for the

reply. I should have been more specific about the problems with the Lookup

component. I wrote that web page and this forum post because I wanted to open up a serious

discussion about best practices for solving this common problem. I have books

and lots of documentation on how the SSIS components work. But I have no good docs on how

to use them to implement common algorithms like this!

There are 2 main problems in

trying to use a Lookup component to detect record changes between a source and

destination databases. We tried and ran into all of them.

(1)

If the source record contains a NULL value in a field then the Lookup component

will send the following where clause to the SQL server

engine:

WHERE

MyDestinationTable.MyField = NULL

This syntax is

invalid but you will not get error. Everything will appear to work properly but

the record will not get updated. Of course this can be worked around using a

bunch of ISNULL() logic but what a pain that is. It's got to be slow

too.

(2) What if one of the fields changes in case only?

For example if the customer name field was changed from "kEVIN hARVICK" to"Kevin

Harvick". This is an obvious "caps lock" error fix that needs to be changed in

the destination database. With the lookup component the where clause will look

like this:

WHERE MyDestinationTable.CustomerName =

'Kevin Harvick'

Because the vast majority of databases are

set to do case insensitive comparisons, the existing value in the table "kEVIN hARVICK" is equal

to the new value "Kevin Harvick". Because of this the change is not detected. I

don't have a reasonable solution to this problem!

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

Is using ISNULL() within a data-flow really a pain though? I guess its a question of taste. I for one would rather write a bunch of SSIS expressions than a whole chunk of code. I know one thing for sure though, it is NOT slow.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

Your technique for using the Lookup component and the

Conditional Split component together is completely different from the way that

we were attempting to do it with just Lookup components only. It looks really

good. I'm going to add a link from my page to you page shortly. I might even

start using your technique in my packages after a little testing.

This is

the first time I have seen this concept. I have seen a lot of blogs talking

about using just the lookup component and glossing over the 2 problems that I

mentioned before.

It looks like your technique might solve the case

sensitivity problem I talked about earlier? It sure looks like it might solve

it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Unfortunately not. You're still left with the problem that the LOOKUP does case-sensitive lookups so it could, as you know, wrongly determine that a record is a new record when in fact it isn't. Once it goes down the "New record" route there isn't much you can do with it other than redo the lookup in a different way (perhaps using your technique or a MERGE JOIN).

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.

Later,
Greg Van Mullem|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Hence, your LOOKUP is still being used to decide on whether or not the row is new or not and hence is susceptible to case-sensitivity. In your case it sounds as though the case-sensitivity issue only affects you when you are seeing whether an existing row has changed or not - in which case this technique WILL help. Conditional Split CAN do case-insensitive lookups.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

Error 2 Validation error. Pump Currency Data: Test for insert or update [1911]: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Package1.dtsx 0 0

anyone can help?

|||

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

End Sub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!

Thanks,
Greg Van Mullem
|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

Great work though.

-Jamie

|||

Jamie,

Thanks for the reply. I should have been more specific about the problems with the Lookup component. I wrote that web page and this forum post because I wanted to open up a serious discussion about best practices for solving this common problem. I have books and lots of documentation on how the SSIS components work. But I have no good docs on how to use them to implement common algorithms like this!

There are 2 main problems in trying to use a Lookup component to detect record changes between a source and destination databases. We tried and ran into all of them.

(1) If the source record contains a NULL value in a field then the Lookup component will send the following where clause to the SQL server engine:

WHERE MyDestinationTable.MyField = NULL

This syntax is invalid but you will not get error. Everything will appear to work properly but the record will not get updated. Of course this can be worked around using a bunch of ISNULL() logic but what a pain that is. It's got to be slow too.

(2) What if one of the fields changes in case only? For example if the customer name field was changed from "kEVIN hARVICK" to"Kevin Harvick". This is an obvious "caps lock" error fix that needs to be changed in the destination database. With the lookup component the where clause will look like this:

WHERE MyDestinationTable.CustomerName = 'Kevin Harvick'

Because the vast majority of databases are set to do case insensitive comparisons, the existing value in the table "kEVIN hARVICK" is equal to the new value "Kevin Harvick". Because of this the change is not detected. I don't have a reasonable solution to this problem!

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

Your technique for using the Lookup component and the Conditional Split component together is completely different from the way that we were attempting to do it with just Lookup components only. It looks really good. I'm going to add a link from my page to you page shortly. I might even start using your technique in my packages after a little testing.

This is the first time I have seen this concept. I have seen a lot of blogs talking about using just the lookup component and glossing over the 2 problems that I mentioned before.

It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

Later,
Greg Van Mullem
|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

anyone can help?

|||

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

End Sub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page

discusses just some of the problems you will run into with the Lookup

component. Let me know what you think!!!

Thanks,
Greg Van Mullem|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

Great work though.

-Jamie

|||

Jamie,

Thanks for the

reply. I should have been more specific about the problems with the Lookup

component. I wrote that web page and this forum post because I wanted to open up a serious

discussion about best practices for solving this common problem. I have books

and lots of documentation on how the SSIS components work. But I have no good docs on how

to use them to implement common algorithms like this!

There are 2 main problems in

trying to use a Lookup component to detect record changes between a source and

destination databases. We tried and ran into all of them.

(1)

If the source record contains a NULL value in a field then the Lookup component

will send the following where clause to the SQL server

engine:

WHERE

MyDestinationTable.MyField = NULL

This syntax is

invalid but you will not get error. Everything will appear to work properly but

the record will not get updated. Of course this can be worked around using a

bunch of ISNULL() logic but what a pain that is. It's got to be slow

too.

(2) What if one of the fields changes in case only?

For example if the customer name field was changed from "kEVIN hARVICK" to"Kevin

Harvick". This is an obvious "caps lock" error fix that needs to be changed in

the destination database. With the lookup component the where clause will look

like this:

WHERE MyDestinationTable.CustomerName =

'Kevin Harvick'

Because the vast majority of databases are

set to do case insensitive comparisons, the existing value in the table "kEVIN hARVICK" is equal

to the new value "Kevin Harvick". Because of this the change is not detected. I

don't have a reasonable solution to this problem!

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

Your technique for using the Lookup component and the

Conditional Split component together is completely different from the way that

we were attempting to do it with just Lookup components only. It looks really

good. I'm going to add a link from my page to you page shortly. I might even

start using your technique in my packages after a little testing.

This is

the first time I have seen this concept. I have seen a lot of blogs talking

about using just the lookup component and glossing over the 2 problems that I

mentioned before.

It looks like your technique might solve the case

sensitivity problem I talked about earlier? It sure looks like it might solve

it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

Later,
Greg Van Mullem|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

anyone can help?

|||

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

End Sub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Thanks,
Greg Van Mullem
|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

Great work though.

-Jamie

|||

Jamie,

There are 2 main problems in trying to use a Lookup component to detect record changes between a source and destination databases. We tried and ran into all of them.

(1) If the source record contains a NULL value in a field then the Lookup component will send the following where clause to the SQL server engine:

WHERE MyDestinationTable.MyField = NULL

WHERE MyDestinationTable.CustomerName = 'Kevin Harvick'

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

This is the first time I have seen this concept. I have seen a lot of blogs talking about using just the lookup component and glossing over the 2 problems that I mentioned before.

It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

Later,
Greg Van Mullem
|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

anyone can help?

|||

PublicOverridesSub AcquireConnections(ByVal Transaction AsObject)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

EndSub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Thanks,
Greg Van Mullem
|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

Great work though.

-Jamie

|||

Jamie,

There are 2 main problems in trying to use a Lookup component to detect record changes between a source and destination databases. We tried and ran into all of them.

(1) If the source record contains a NULL value in a field then the Lookup component will send the following where clause to the SQL server engine:

WHERE MyDestinationTable.MyField = NULL

WHERE MyDestinationTable.CustomerName = 'Kevin Harvick'

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

This is the first time I have seen this concept. I have seen a lot of blogs talking about using just the lookup component and glossing over the 2 problems that I mentioned before.

It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

Later,
Greg Van Mullem
|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

anyone can help?

|||

PublicOverridesSub AcquireConnections(ByVal Transaction AsObject)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

EndSub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

how to do this in SSIS? soory if i m a noob

Hi all,

Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.

I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):

After 1st run,

Source table has: 1 - 1000 records

Destination table has: 1 - 1000 records

For 2nd run,

Source table has: 1 - 1500 records

Destination table has: 1 - 1500 records

How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?

Many thanks for any help provided.

This article explains how to decide whether a row already exists in the destination or not and then filter accordingly: http://www.sqlis.com/default.aspx?311

Get that but working first and then we'll tackle how to look for changes (tip: You can use a LOOKUP transform)

-Jamie

|||I have a really good method fully documented at the following URL.

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

Thanks,
Greg Van Mullem
|||

Greg Van Mullem wrote:
I have a really good method fully documented at the following URL.
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
It uses the script component instead of the Lookup component. The Lookup component is really problematic. The bottom of this page discusses just some of the problems you will run into with the Lookup component. Let me know what you think!!!
Thanks,
Greg Van Mullem

Copied from another thread

Hi Greg,

Fascinating stuff. You've got some really valuable code to share up there.

Great work though.

-Jamie

|||

Jamie,

There are 2 main problems in trying to use a Lookup component to detect record changes between a source and destination databases. We tried and ran into all of them.

(1) If the source record contains a NULL value in a field then the Lookup component will send the following where clause to the SQL server engine:

WHERE MyDestinationTable.MyField = NULL

WHERE MyDestinationTable.CustomerName = 'Kevin Harvick'

Thanks,

Greg Van Mullem

|||

Greg,

You're absolutely right. Case insensitive comparisons are definitely a problem when using LOOKUP and that's where your solution really does come into its own.

In the meantime, I've written a friendly retort here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Jamie,

This is the first time I have seen this concept. I have seen a lot of blogs talking about using just the lookup component and glossing over the 2 problems that I mentioned before.

It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?

Thanks,
Greg Van Mullem

|||

Greg Van Mullem wrote:

Jamie,
It looks like your technique might solve the case sensitivity problem I talked about earlier? It sure looks like it might solve it?
Thanks,
Greg Van Mullem

Greg,

Great discussion though. Its great to get these issues out in the open.

-Jamie

|||Jamie,

Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?

Later,
Greg Van Mullem
|||

Greg Van Mullem wrote:
Jamie,
Actually it might work prefectly for my needs if the Conditional Split component does a case sensitive compare?
All of my primary / surrogate keys that I compare to determine if a record exists or not are integers. Using your technique these are the only values that the lookup componment would compare. The Conditional Split component would compare all the other varchar values.
Later,
Greg Van Mullem

Remember though that it is the LOOKUP that determines whether the row is new or existing.

The CONDITONAL SPLIT determines, if a row already exists, whether it has been changed or not.

Lots of options. Lots of considerations. That's what I love about SSIS though - there's usually more than one way of achieving something.

-Jamie

|||

thanks guys...I need to try it out and will feedback here for updates on my situation.

I really appreciate the help I get here. :)

-Daren

|||

i try the method at here

http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

but i got two errors

Error 1 Validation error. Pump Currency Data: DTS.Pipeline: input column "CodeISOnum" (1039) has lineage ID 422 that was not previously used in the Data Flow task. Package1.dtsx 0 0

anyone can help?

|||

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection1

sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection) -> error 2

End Sub

i think error 2 which i encountered has something to do with the line above, but how to resolve it?

|||

I have seen error #2 before. Something is wrong with the connection manager and/or the connection. Verify that #6 on my list of steps is configured correctly.

Thanks,
Greg

|||

I also got these errors in my Script component...I search around microsoft.support pages but cannot find anything useful. sighed.

Warning 1 The dependency 'EnvDTE' could not be found.

Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Friday, March 30, 2012

How to evaluate the performance of the sql server

How to evaluate the performance of the sql server

Wednesday, March 28, 2012

How to evaluate the performance of the sql server

How to enhance the performance when there is bulk update in trigge

How to enhance the performance when there is bulk update in tr

How to enhance the performance of a table

How to enhance the performance of a table

How to enhance the performance of a table

Wednesday, March 21, 2012

How to effectively troubleshoot?

Wednesday, March 7, 2012

how to do this in SSIS? soory if i m a noob

how to do this in SSIS? soory if i m a noob

how to do this in SSIS? soory if i m a noob

how to do this in SSIS? soory if i m a noob

how to do this in SSIS? soory if i m a noob

how to do this in SSIS? soory if i m a noob

multiple rows insert

Blog Archive

About Me