Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-03-11 : 12:47:08
|
| I have an SSIS that is attempting to increase the amount of data contained in all tables. So each table pumps its own data back into itself. The database consists of a set of new tables that are created each day. Each set of tables are identical, and have the date suffixed to the end of the table name. Each set of tables consists of one header table, and 17 child tables (1 parent, and 1 nest level).CUt down example:Set of tables 1Header_20090101Item_20090101Tender_20090101Set of tables 2Header_20090102Item_20090102Tender_20090102Set of tables 3Header_20090103Item_20090103Tender_20090103There is one data flow for each table:HeaderItemTenderThis data flow sits in a foreach loop, so each identical table in each set of tables is populated:FOreach loop 1:Data flow 1Header_20090101, Header_20090102, Header_20090103FOreach loop 2:Data flow 2Item_20090101, Item_20090102, Item_20090103FOreach loop 3:Data flow 3Tender_20090101, Tender_20090102, Tender_20090103 However, the package keeps cancelling itself when populating the child tables:SSIS package "ExtrapolateNumberOfStores.dtsx" finished: Canceled.But its not finished. There is no consistency as to when it stops, but the Header table is always populated, and then perhaps part/some rows in the child tables.The event log reads:EventType sql90exception, P1 dtsdebughost.exe, P2 2005.90.4035.0, P3 492b1a4d, P4 txlookup.dll, P5 2005.90.4035.0, P6 492b1a32, P7 0, P8 00010411, P9 00000000, P10 NIL.The SQLDumper flashes up just before it cancels. I have done some research, and have tried the following:Installed windows updatesInstalled latest service packHave moved a copy of the DB to my local machine and tried it thereReduced the size of the DB substantially (to only a few thousand rows) - as initially it was massive!The weird thing is, is that the SSIS Package works on another database that is the same in structure , although the data is different(some different table names ,different dates, but identical structures and created from the same set of scripts, and some different tables are populated. Eg. Header, item, NOT Tender, etc. There are, however, only 5 sets of tables in the one that works, whereas the one falling over has 12 sets?)?I thought it may be a data thing, so disabled all the tables, and enabled each one, one by one, and noted any that cancelled when enabled:Run 1: Enabled child data flow 1Run 2: Enabled child data flow 1 & 2Run 3: Enabled child data flow 1, 2 & 3Run 4: Enabled child data flow 1, 2, 3 & 4...... etcI then went back, and enabled ONLY 1 child table that failed at a time, and NOT ONE fell over.Other threads talk about network issues,etc. But this cannot be the explanation, as why would the package work on one DB and not the other, and they both reside on the same server.I am so stuck, and have been wasting precious time trying to resolve this issue, but to no avail. I am now DESPERATE!!!Please please please can anyone shed some light on this issue?Hearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-13 : 11:49:53
|
| If anyone suffers from the same issue, I ended up wasting 2 days trying to resolve with no results. In the end, I had to install SQL Server 2008. It then worked. So this must be an issue 2005 SSIS! I just hope no-one else suffers the same pain!!Hearty head pats |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-13 : 11:49:53
|
| If anyone suffers from the same issue, I ended up wasting 2 days trying to resolve with no results. In the end, I had to install SQL Server 2008. It then worked. So this must be an issue 2005 SSIS! I just hope no-one else suffers the same pain!!Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-03-16 : 07:15:40
|
| Just for other peoples reference if they search on the same topic...............SQL2008 did not solve the problem. However, rather than cancelling, it did generate a useful error message regarding not being able to read an input row into the buffer.Bascially, my package did not hve enough memory to perform all the required processing. Other than bumping up the hardware, I tried to fiddle around with various config values, to no avail.Eventually, i had to remove ALL the child table data flow components into their own packages, and these were called by the parent. Apparently, if each data flow resides in its own process, they each will have acces to 2GB of virtual memory, whereas beforehand, all 18 dataflows were sharing the same assigned virtual memory, hence stopping when it reashed the threshold.Its still not great, as it now seems to run forever to only populate a relatively small amount of data...............!Very frustrating.Hearty head pats |
|
|
|
|
|
|
|
|
SSIS Package just cancels itself