Iterating Data Movement

Hi all

I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.

I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:

1) It runs in a tight loop and consequently degrades system performance enormously.

2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.

Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.

All help is appreciated.

Jays :-)

I think you want a trigger on the table so you know when the data in that table is changed, and copy it accordingly.

|||

Hi Christian

I would like to avoid that if I can.

My package has custom logging that writes a set of audit records each time it is run. This would result in a LOT of these records being written throughout the day when one such set would do.

Thanks

Jays :-)

It takes a long time to insert the first record each time when the program start

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

However, when I give up the dataset and using the following code:

cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount

I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

Last example does not include looking for updated rows in the DataSet and generating appropriate commands. Since it does less, it's faster.

sql

It may need to be a trigger

Hi,

I needed to perform an update of a database table row when another table gets an insert.

Would I use a trigger to do this?, If so, how?

I will need to pass two of the feilds just inserted to perform the update on the second table.

Yes, you can use a trigger to do that.

Issue with Page Breaking

I'm having an issue with Page Breaks within a SQL Report. The report
consisting of a single table with two groups. The report is setup to
insert a page break after the first group. Grand totals are calculated
in the footer of the main table. The report renders correctly in HTML.
When the report is exported to PDF, the Grand Totals section is render
on it's own page. The Grand Totals should render right after the last
group on the report. Is there a way in SQL Reporting to prevent the
last page break on a report?
Report Group Layout
Single table containing the the following:
Table Header
Group Header 1 - Page Break after group
Group Header 2
Detail Row -
Group Footer 2 - Contains Subtotals
Group Footer 1 - Contains Subtotals
Table Footer - Grand Total
Thanks
ChadHi, make sure that the page width and height is big enough for the 2nd page
to fit in. In layout, go to Report, Report Properties, Layout. Play around
with the Page Width and Page Height (increasing height most likely) until you
come right.
"Chad McKee" wrote:
> I'm having an issue with Page Breaks within a SQL Report. The report
> consisting of a single table with two groups. The report is setup to
> insert a page break after the first group. Grand totals are calculated
> in the footer of the main table. The report renders correctly in HTML.
> When the report is exported to PDF, the Grand Totals section is render
> on it's own page. The Grand Totals should render right after the last
> group on the report. Is there a way in SQL Reporting to prevent the
> last page break on a report?
>
> Report Group Layout
> Single table containing the the following:
> Table Header
> Group Header 1 - Page Break after group
> Group Header 2
> Detail Row -
> Group Footer 2 - Contains Subtotals
> Group Footer 1 - Contains Subtotals
> Table Footer - Grand Total
> Thanks
> Chad
>|||Hello,
I have checked the Page Width and Height. Everything fits within the
page size. If I remove the "Page Break after group" on the first group.
Everything renders without the extra page break. I think something is
wrong with the "Page Break after group" when rendering to PDF.
Thanks
Chad
jagberg wrote:
> Hi, make sure that the page width and height is big enough for the 2nd page
> to fit in. In layout, go to Report, Report Properties, Layout. Play around
> with the Page Width and Page Height (increasing height most likely) until you
> come right.
> "Chad McKee" wrote:
>
>>I'm having an issue with Page Breaks within a SQL Report. The report
>>consisting of a single table with two groups. The report is setup to
>>insert a page break after the first group. Grand totals are calculated
>>in the footer of the main table. The report renders correctly in HTML.
>> When the report is exported to PDF, the Grand Totals section is render
>>on it's own page. The Grand Totals should render right after the last
>>group on the report. Is there a way in SQL Reporting to prevent the
>>last page break on a report?
>>
>>Report Group Layout
>>Single table containing the the following:
>>Table Header
>> Group Header 1 - Page Break after group
>> Group Header 2
>> Detail Row -
>> Group Footer 2 - Contains Subtotals
>> Group Footer 1 - Contains Subtotals
>>Table Footer - Grand Total
>>Thanks
>>Chad|||This is a known limitation in our PDF renderer. I don't know of a
workaround, though someone more creative than me in this group might have
one up their sleeves. :)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chad McKee" <cmckee@.startech-comp.com> wrote in message
news:uTHDB67MFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm having an issue with Page Breaks within a SQL Report. The report
> consisting of a single table with two groups. The report is setup to
> insert a page break after the first group. Grand totals are calculated in
> the footer of the main table. The report renders correctly in HTML. When
> the report is exported to PDF, the Grand Totals section is render on it's
> own page. The Grand Totals should render right after the last group on
> the report. Is there a way in SQL Reporting to prevent the last page
> break on a report?
>
> Report Group Layout
> Single table containing the the following:
> Table Header
> Group Header 1 - Page Break after group
> Group Header 2
> Detail Row -
> Group Footer 2 - Contains Subtotals
> Group Footer 1 - Contains Subtotals
> Table Footer - Grand Total
> Thanks
> Chad|||Hello Donovan,
Thanks for the reply. Do you know if/when this issue is going to be
fixed? Will the fix be included within the next Service Pack for
Reporting Services.
Thanks
Chad
Donovan Smith [MSFT] wrote:
> This is a known limitation in our PDF renderer. I don't know of a
> workaround, though someone more creative than me in this group might have
> one up their sleeves. :)
>|||Sorry, this won't make it into RS 2000 SP2 and is not currently on our plate
for Yukon (RS 2005).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chad McKee" <cmckee@.startech-comp.com> wrote in message
news:425ACA37.5090704@.startech-comp.com...
> Hello Donovan,
> Thanks for the reply. Do you know if/when this issue is going to be
> fixed? Will the fix be included within the next Service Pack for
> Reporting Services.
> Thanks
> Chad
> Donovan Smith [MSFT] wrote:
>> This is a known limitation in our PDF renderer. I don't know of a
>> workaround, though someone more creative than me in this group might have
>> one up their sleeves. :)|||Hi Donovan,
Do you know where I can find a workaround for this problem?
Thanks
Chad
Donovan Smith [MSFT] wrote:
> Sorry, this won't make it into RS 2000 SP2 and is not currently on our plate
> for Yukon (RS 2005).
>|||I am encountering a similar problem when displaying/printing a .pdf file from
a report containing a sub report. I have tried placing the sub report in both
rectangles and lists. I've adjusted all the margins (left, right, top and
bottom) to 0 in the sub report. There is a property called KeepTogether in a
list which I've set to both true and false without any impact. I can't seem
to find any property of any control that enables text in a sub report to span
multiple pages. I've also played with the page width and page height of the
sub report to no avail. Any help would be appreciated
"Chad McKee" wrote:
> Hi Donovan,
> Do you know where I can find a workaround for this problem?
> Thanks
> Chad
> Donovan Smith [MSFT] wrote:
> > Sorry, this won't make it into RS 2000 SP2 and is not currently on our plate
> > for Yukon (RS 2005).
> >
>|||Sorry, no. It's a limitation with our renderer. I don't have a creative
RDL solution though someone else in this group might.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chad McKee" <cmckee@.startech-comp.com> wrote in message
news:425AD6E0.9000201@.startech-comp.com...
> Hi Donovan,
> Do you know where I can find a workaround for this problem?
> Thanks
> Chad
> Donovan Smith [MSFT] wrote:
>> Sorry, this won't make it into RS 2000 SP2 and is not currently on our
>> plate for Yukon (RS 2005).|||Donovan with all due respect this issue should be easy to resolve. It's a
simple conditional check to render the table footer output on last page
following a group break.
I suspect that literally 1000's if not 10,000's of your customers are going
to be having the same issues since this is a very common scenario. If that's
justification enough for giving this a higher priority I don't know what is.
Send me the source code and I would be glad to fix... and I'm being serious!
Regards,
Kevin Weir
OmniLogic Systems
www.omnilogic.net
"Donovan Smith [MSFT]" wrote:
> Sorry, no. It's a limitation with our renderer. I don't have a creative
> RDL solution though someone else in this group might.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chad McKee" <cmckee@.startech-comp.com> wrote in message
> news:425AD6E0.9000201@.startech-comp.com...
> > Hi Donovan,
> >
> > Do you know where I can find a workaround for this problem?
> >
> > Thanks
> > Chad
> >
> > Donovan Smith [MSFT] wrote:
> >> Sorry, this won't make it into RS 2000 SP2 and is not currently on our
> >> plate for Yukon (RS 2005).
> >>
>
>|||I agree with Kevin. Its a very common practice to have page breaks
after groups in a report containing a grand total. I just ran into the
issue again with a different report yesterday. This should be moved up
the priority list.
By the way. There is no way of working around the issue since you can't
place expressions on the page break property.
Thanks
Chad
Kevin Weir wrote:
> Donovan with all due respect this issue should be easy to resolve. It's a
> simple conditional check to render the table footer output on last page
> following a group break.
> I suspect that literally 1000's if not 10,000's of your customers are going
> to be having the same issues since this is a very common scenario. If that's
> justification enough for giving this a higher priority I don't know what is.
> Send me the source code and I would be glad to fix... and I'm being serious!
> Regards,
> Kevin Weir
> OmniLogic Systems
> www.omnilogic.net
>
> "Donovan Smith [MSFT]" wrote:
>
>>Sorry, no. It's a limitation with our renderer. I don't have a creative
>>RDL solution though someone else in this group might.
>>--
>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>"Chad McKee" <cmckee@.startech-comp.com> wrote in message
>>news:425AD6E0.9000201@.startech-comp.com...
>>Hi Donovan,
>>Do you know where I can find a workaround for this problem?
>>Thanks
>>Chad
>>Donovan Smith [MSFT] wrote:
>>Sorry, this won't make it into RS 2000 SP2 and is not currently on our
>>plate for Yukon (RS 2005).
>>
>>|||I Thought I'd chime in with another vote for pushing this to the top of the
request heap. It is the main issue we've had with RS. We were going to
migrate all our reports from Crystal until we found grouping/page break
problems. I guess we'll keep writing Crystal / Business Objects until
Microsoft wakes up and realizes that this issue is a deal breaker. Even the
lowly Access Report Writer has this built in.
"Chad McKee" wrote:
> I agree with Kevin. Its a very common practice to have page breaks
> after groups in a report containing a grand total. I just ran into the
> issue again with a different report yesterday. This should be moved up
> the priority list.
> By the way. There is no way of working around the issue since you can't
> place expressions on the page break property.
> Thanks
> Chad
> Kevin Weir wrote:
> > Donovan with all due respect this issue should be easy to resolve. It's a
> > simple conditional check to render the table footer output on last page
> > following a group break.
> >
> > I suspect that literally 1000's if not 10,000's of your customers are going
> > to be having the same issues since this is a very common scenario. If that's
> > justification enough for giving this a higher priority I don't know what is.
> > Send me the source code and I would be glad to fix... and I'm being serious!
> >
> > Regards,
> > Kevin Weir
> > OmniLogic Systems
> > www.omnilogic.net
> >
> >
> >
> > "Donovan Smith [MSFT]" wrote:
> >
> >
> >>Sorry, no. It's a limitation with our renderer. I don't have a creative
> >>RDL solution though someone else in this group might.
> >>
> >>--
> >>This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
> >>"Chad McKee" <cmckee@.startech-comp.com> wrote in message
> >>news:425AD6E0.9000201@.startech-comp.com...
> >>
> >>Hi Donovan,
> >>
> >>Do you know where I can find a workaround for this problem?
> >>
> >>Thanks
> >>Chad
> >>
> >>Donovan Smith [MSFT] wrote:
> >>
> >>Sorry, this won't make it into RS 2000 SP2 and is not currently on our
> >>plate for Yukon (RS 2005).
> >>
> >>
> >>
> >>
>|||Hi guys I have had this issue with this.
I have discoverd two work arounds for this
1. Put everything in the group headers and simply don't use the group
footers. The extra page breaks problem vanish. I rather have Totals in the
footers below the detail but this avoids the problem
OR
2. Select the Repeat Group Footers on each page option (which looks a
little crowded) but will give your report a little "running total" look to
it.The extra page breaks problem vanishs once again.
--
Message posted via http://www.sqlmonster.com|||We really shouldn't have to preform work arounds for functionally like
page breaking. These work arounds may work in some situations, but not
all. Here is another instant of breaking not working.
I have a report that contains four tables. The second to the last table
has the page break at start set to true. The report renders correctly
and inserts page breaks in the correct places.
Now I need to add an expression to the table's visible property to hide
the table on specific records. The expression works, but when the table
is visible the page break is ignored. I'm not sure why an expression on
the visible property causes the page breaks to stop working.
There seem to be issues with page breaks in general.
Thanks
Chad
Paul Mauriello via SQLMonster.com wrote:
> Hi guys I have had this issue with this.
> I have discoverd two work arounds for this
> 1. Put everything in the group headers and simply don't use the group
> footers. The extra page breaks problem vanish. I rather have Totals in the
> footers below the detail but this avoids the problem
> OR
> 2. Select the Repeat Group Footers on each page option (which looks a
> little crowded) but will give your report a little "running total" look to
> it.The extra page breaks problem vanishs once again.
>|||I use a workaround to do RunningValue counts over a group and then show
the GrandTotal on the last page when the RunningValue count for the
recordset equals the Count of all records in the recordset.
This is an unacceptable workaround for this product. Grand Totals
should have the "Keep Together" option with the last group. This is
seriously slowing down development.

Issue with OLE DB Command while writing to DB2 database

Hi,

I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.

The same package works fine when the target is on Oracle database or a SQL Server database.

For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.

Any suggestion regarding this would be really helpful.

Thanks,

Manish

Some thoughts on this:

1. It is better to use the OLE DB Destination to insert data into a table instead of the OLE DB Command with an insert statement. You should give that a try.

2. Are you using the Microsoft OLE DB provider for DB2 from our SQL Server 2005 Feature Pack? That's the provider that's been tested with SSIS.

3. I don't understand what you mean by not able to get input columns. What are the error messages when the package fails?

|||

Yes, I am using the Microsoft DB2 drivers only. Using the OLE DB Destination, it worls only for insert. But, I wanted to simulate a scenario, where we do, both insert and update to the target table. So, we used OLE DB Command, and wrote the insert/update query.

The same works fine on Oracle and SQL Server target database. But on DB2, it doesnt work, as in the OLE DB command, we need to map the input columns to the parameters value. The drivers are not able to fetch the input data, and everytime, it insert NULL as it is not able to get the input values.

Let me know if you need any more information on the same.

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

Issue with float data type

Hi,

I have a table with float data type. When we insert or update data with some math(1+1.01) it converts the decimal value to approximate value.

Example:

Create a table say "Table1" with field "Field1" of data type float

Now insert a record with value 1.01

Select * from Table1 would give me out put at 1.01

Update Table1 set Field1 = 1.01+1

Now Select * from Table1

Instead of returning me 2.01, it returns 2.0099999999999998. Same thing happens if I insert record with following statement

insert into Table1(Field1) values (1+1.01)

Any idea why its doing this way? I know float is approximate data type, but shouldn't it be giving me correct value for what I am doing? Is there any server setting that I can change to get proper result?

Thanks,

Hiten:

I think the answer to your question is that there is no exact conversion of the number 2.01 into a hexidecimal floating point representation; what I remember is that it is initially surprising what numbers do not convert exactly and this is probably one of them. I think the answer is you need to live with the limitations of floating point representation or switch to an exact representation with a pre-defined precision and scale. If you stick with float, use of the ROUND function will be helpful. I verified and I receive the same behavior and same response that you do. Somebody check me on this?

Dave

|||

Hiten:

I found this article:

http://docs.python.org/tut/node16.html

The "B.1" section discusses the problem of representing 1/10 in binary; this should give the idea.

Dave

|||

Float is an approximate data type so it stores only close approximation of a value. See below link for more details:

http://msdn2.microsoft.com/en-us/library/ms187912.aspx

You need to use decimal or numeric to get exact representation of the value.

|||

Hi all,

What are the drawbacks of using decimal insted of float data type?

Thanks.

issue oledb

I use oledb to insert the data to respective tables..

then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...

It will help us help you if you post that error.|||

Here is the error

[Lookup 3 [39523]] Error: Row yielded no match during lookup.

[Lookup 3 [39523]] Error: The "component "Lookup 3" (39523)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (39525)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup 3" (39523) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

this is what i do..and shows the about error mentioned above..

After inserting using oledb command in the lookup i look up for two columns and then get respective key field to that columns and then i insert it to other table using oledb

|||

The Lookup component treats the no matches as errors; that is what you are seeing. Are you expecting to have a match for every row that pass trhough the Lup component?

if so; make sure the mapping inside of the lookup is right and the table/query that supports it has all the expected values.

If not; configure the error output of the component to either redirect or ignored the no matches.

|||

yes i am looking to see if there is a match using lookup.There should be a match as i am inserting the records just before having a lookup...But the lookup shows error(red). i have been looking for data in the table and the lookup which i am pointing to...they dont have any problem...one possible reson might be the lookup is running even before respective record is been inserted as oledb command is slow and lookup is fast..

|||

With the default settings, the lookup will cache all of the data as soon as the dataflow begins. If you are inserting the records in the dataflow, the lookup will not have them in cache. You can avoid this by going to the Advanced tab in the lookup editor and selecting Enable Memory Restriction. This will have an impact on performance, as now the lookup will query the database for each row passing through it.

If you have a lot of repeated values that you are trying to look up, you could play with the Enable caching options to see if you can get decent performance. With this option set, it doesn't cache anything at the start, but rows are cached as they are looked up from the source. If it doesn't find the row in cache, it will check the database.

|||thank You for your prompt reply.....this seem to work now....I have one more question for you...i use oledb as i transform incoming data to multiple tables at a time....which is very slow.....what would be the best solution for fast performance? if script component how do i need to do it please let me know how to use this or any links would help..|||

Please mark the answers that were helpful.

Have you looked at using a multicast with several OLEDB destinations?

|||No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..|||I just want to increase the performance since oledb is slow...|||

sureshv wrote:

No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..

You should have a source component connected to the multicast. You can then drag multiple outputs from the multicast to your destinations (OLEDB Destinations, not OLEDB Commands).

|||hey is there a way to enter some of the incoming data into one table and get the key to it and then insert the rest of the data along with the key into another table...I do it using oledb command which works...but is there any other way i can implement it which is faster...

issue oledb

I use oledb to insert the data to respective tables..

then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...

It will help us help you if you post that error.|||

Here is the error

[Lookup 3 [39523]] Error: Row yielded no match during lookup.

[Lookup 3 [39523]] Error: The "component "Lookup 3" (39523)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (39525)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup 3" (39523) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

this is what i do..and shows the about error mentioned above..

After inserting using oledb command in the lookup i look up for two columns and then get respective key field to that columns and then i insert it to other table using oledb

|||

The Lookup component treats the no matches as errors; that is what you are seeing. Are you expecting to have a match for every row that pass trhough the Lup component?

if so; make sure the mapping inside of the lookup is right and the table/query that supports it has all the expected values.

If not; configure the error output of the component to either redirect or ignored the no matches.

|||

yes i am looking to see if there is a match using lookup.There should be a match as i am inserting the records just before having a lookup...But the lookup shows error(red). i have been looking for data in the table and the lookup which i am pointing to...they dont have any problem...one possible reson might be the lookup is running even before respective record is been inserted as oledb command is slow and lookup is fast..

|||

With the default settings, the lookup will cache all of the data as soon as the dataflow begins. If you are inserting the records in the dataflow, the lookup will not have them in cache. You can avoid this by going to the Advanced tab in the lookup editor and selecting Enable Memory Restriction. This will have an impact on performance, as now the lookup will query the database for each row passing through it.

If you have a lot of repeated values that you are trying to look up, you could play with the Enable caching options to see if you can get decent performance. With this option set, it doesn't cache anything at the start, but rows are cached as they are looked up from the source. If it doesn't find the row in cache, it will check the database.

|||thank You for your prompt reply.....this seem to work now....I have one more question for you...i use oledb as i transform incoming data to multiple tables at a time....which is very slow.....what would be the best solution for fast performance? if script component how do i need to do it please let me know how to use this or any links would help..|||

Please mark the answers that were helpful.

Have you looked at using a multicast with several OLEDB destinations?

|||No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..|||I just want to increase the performance since oledb is slow...|||

sureshv wrote:

No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..

You should have a source component connected to the multicast. You can then drag multiple outputs from the multicast to your destinations (OLEDB Destinations, not OLEDB Commands).

|||hey is there a way to enter some of the incoming data into one table and get the key to it and then insert the rest of the data along with the key into another table...I do it using oledb command which works...but is there any other way i can implement it which is faster...

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank