I have a SSIS package where an Excute SQL Task creates a temporary table and at the end of the package another one deletes the table (DROP myTable). I also have some Execute SQL Tasks that do INSERT statements were they insert data into a production table from the temporary table. The problem is that although the package throws no errors, it seems that he Execute SQL Tasks are not running. The one at the end does not delete the temp table because when the package runs again, the temp table is still there and the package fails with a "table already exists" error. Yet the previous run of the package had no problems. I have also found several records in the database that were NOT inserted into the production table from the temporary one. Again, this is from a run that generated no errors. I am using SQL Server 2008 and the database I am working with in SSIS is Access 2000. JamesNT"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

First question: Is this a TRUE temp table? (as in "CREATE TABLE #MyTempTable") or is it a regulare table that just gets used temporarily? If, as in most cases, you can be assured that there will only be one instance of the package running at any time, then why not leave the table structure in the database and just TRUNCATE it before loading? I mean, really, how much room does a single table with no rows take up in your database? Maybe even create a dedicated schema (we have one called "Stage") for tables such as this so everybody knows their usage. This way, you would execute one task at a time and watch the rows in that Staging table as you step through each task. (Very usefull diagnostic technique).Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd, Thanks for your reply. This is a true table in that the Execute task that creates it runs the following query: CREATE TABLE TempWip ( PatientID char(6), BillID char(6), ChargeID char(7), Remaining CURRENCY, crTransactionID TEXT(10), CreditID TEXT(7), DoS DATETIME ) I create then delete this table because the Line of Business application we use has a set of database utilities we have to run occassionally and one of the steps in the utilities deletes any tables the application didn't install. I have no idea why the utility in the app does this as I didn't write it, I just have to deal with it - and no there is no way to turn it off and we do have to run these utilities (they check and fix other issues). Therefore, I create and delete any temporary tables I use to ensure I know when they are there and when they are not there. This approach avoids failed packages and spending time writing additional error checking for when a table may or may not be there. The applications database utilities are not removing the temp table as I am trying to use it as my package runs at night and the utilities have to be run by a human manually (you have to click FILE - DATABASE UTILITIES - Check/Verify Balances, etc.) JamesNT"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

did you checked the package in BIDS? did you see any error while running the package under BIDS? http://www.rad.pasfu.com

Reza Raad, Thanks for your response. Watching the package run in BIDS was the very first thing I did. In BIDS it runs perfectly. All boxes green. Of course, everything runs great when the mechanic is looking - hence my posting here. One other piece of information, the package is reaching out to an Access 2000 database via a file share. Is it possible that Access may return a success when in fact it did nothing? That is, in fact, what I am really asking here. James"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

I don't think so, Could you provide information about your execute sql task configurations, or provide screen shots here?http://www.rad.pasfu.com

BTW: This is NOT a TRUE temp table. TRUE Temp tables are prefaced by the # sign and exist ONLY for the duration of the database session. AND, they get created in the temp database. Yours is simply a table that gets created temporarily for the package. The difference is that if your package fails half-way through, then the DROP statement will not get executed, leaving this table out there. If you run it again, you will get an error on the CREATE statement, because the table would already exist. I would do two things: 1) put in a statement in the Task that CREATES the table BEFORE the CREATE statement. Have it check the existence of the table and if it finds it, then DROP it. (Such script can be generated easily by using SSMS and right-clicking on a table, then select Script As >> DROP and Re-Create to >> New Window.) 2) I would also include a schema name in front of the table name in all places where it is referenced. Why? because if this package gets run by some account that has a different Default Schema than the one you are assuming, then the table gets created under that schema. If your DFT thinks the table is in DBO, but it's not, well . . . 3) (OK, three things) Put in some Error Handling and add an Execute SQL Task that drops the table if it exists. That way, the package will clean up its own mess on any failure. Hope this helps. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd, Please remember the database I am working with is Access 2000 - not a SQL Server database. I was under the impression that temp tables and having different schemas were not features of Access. JamesNT "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

Reza Raad, Here is a screen shot of the Execute SQL Task Properties: ScreenShot JamesNT"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

Access, eh? OK, then design a True/False test that will see if the table even exists. If it does, then truncate it, if not, then CREATE it. You cannot go on the assumption that your table will ALWAYS be there or ALWAYS NOT be there in this particular case. You would need to test for its existence somehow.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Reza Raad, Here is a screen shot of the Execute SQL Task Properties: ScreenShot JamesNT "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen As I see the execute Task is DISABLED! ( the disable property is true , second property in the list ) . if a task be disabled then this task will not run. set disable to false and try again.http://www.rad.pasfu.com

Reza, My apologies. The task is ENABLED. It was disabled when I took the screen shot because I was working on something else. Sorry for the confusion. JamesNT"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

Todd, Access, yes, I know - don't get me started. SQL Server is where it's at. I agree - I'm going to have to test for the existance of the table. But what about the Execute SQL task that runs the INSERT query? It seems like it isn't inserting anything (and there are records to insert) yet the package runs successfully... Is there any situation where an Execute SQL task can run against an Access database, return success, but not do its job? JamesNT"If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

Access, yes, I know - don't get me started. SQL Server is where it's at. Is there any situation where an Execute SQL task can run against an Access database, return success, but not do its job? I started my database career with Access 2.0 so I'll not bad-mouth it too much. :-) Look into SQL Server Express (2005 or 2008). It's FREE and can support databases up to 4 Gig in size. Has everything you need: Triggers, Stored Procedures. And did I mention it's FREE? Your INSERT statement, I assume it SELECTS some list of records. Well, if that SELECT statement yields no records, then none will get inserted. Verify your SELECT portion of the INSERT statement. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Todd, We are moving over to SQL 2008 Standard but that is going to take some time (and it's obvious we already have it since I am asking a SSIS question :) It appears, and forgive me for this, that a previous step is taking all the data out of the temp table before the INSERT statement runs because, oddly, all the PRODUCTION data matches the criterion to be removed. This is very strange. And, a story for another day and for someone else to answer (e.g. the person/idiot who gave me the criteria). JamesNT "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen

Your INSERT statement, I assume it SELECTS some list of records. Well, if that SELECT statement yields no records, then none will get inserted. Verify your SELECT portion of the INSERT statement. Hope this helps. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate. Rule of thumb: If a step X is not yielding the desired results, don't just get stuck examining step X. What kind of mess are previous steps U, V, and W leaving step X in? JamesNT "If you have to ask about various operating system limits, you're probably doing something wrong." -Raymond Chen