February 4, 2010 at 12:46 pm
I have a strange problem which I think might be related to some kind of cahcing mechanism; however, I cannot identify the nature of this actual issue. All I'm hoping is perhaps someone else has encountered this problem.
First Environment - Local System as outlined below:
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200) MS .NET Framework Version 2.050727 SP2
Microsoft Visual Studio 2005 Tools For Applications 77633-163-9000041-41698
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
OS Manufacturer Microsoft Corporation
System Name MySystem
System Manufacturer Dell Inc.
System Type X86-based PC
Processor x86 Family 6 Model 23 Stepping 10 GenuineIntel ~2792 Mhz
BIOS Version/Date Dell Inc. A03, 4/29/2009
SMBIOS Version 2.5
----------------------
Nature Of Problem:
I took over development of a DW SSIS package.
The first part of the package has an import data container with 3 Execute SQL Tasks. The first part of the process in this whole and these specific 3 Execute SQL Tasks is create the temporary tables used throughtout the rest of the project.
There was a table layout change in the core business logic.
In my SQL Prepare Statement:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_MyTable]
GO
CREATE TABLE [dbo].[TEMP_MyTable](
[My_ID] [float] NULL,
[Other ID] [float] NULL,
[Official ID Name] [nvarchar](255) NULL
) ON [PRIMARY]
The new input table would be like this:
CREATE TABLE [dbo].[TEMP_MyTable](
[My_ID] [float] NULL,
[Other ID] [float] NULL,
[Official ID Name] [nvarchar](255) NULL,
[Aux Desc] nvarchar(255) NULL,
[Active] char(1) etc... more columns here.
) ON [PRIMARY]
I have gone physically to the database and removed the original table during testing.
I have a task that takes table from an input source and will push to this temp table. The temp table is used elsewhere for processing in procedures.
I have copied this code several times to NotePad+++ from the Enter SQL Query window. Upon pasting that code back into that window, I have physically deleted the code first and then pasted the changes made in NotePAd+++ back into the Enter SQL Query window. I have then verified the create table layout with my new fields is present.
For some reason, everytime I execute this Task, the original table layout is created.
So I physically delete that temp table, I then re-check my code in NotePad +++ and I see my code does contain my new table definitions, re-paste that code. Re-run and again, I get the same original table layout in my code.
I'm pulling my hair because this should not be happening, this should not be that hard and I don't know what else could cause this.
February 4, 2010 at 1:03 pm
Ok, am assuming a typo but are you *sure* the table is being recreated and it's not erroring out?
The DROP is dropping a different table :hehe:
Steve.
February 4, 2010 at 1:08 pm
Follow Up Troubleshooting:
I removed all of the script in that task.
I saved the entire package.
I delete all of the objects in the database that task created.
I then executed that one task.
The tables continue to be created including the incorrect table layout.
February 4, 2010 at 1:10 pm
Steve, sorry. I changed the descriptions of the tables. I forget to change that in my description. Me bad.
Yes, I have checked that.
February 4, 2010 at 1:11 pm
What I posted was just an example, the actual code involves tables with 150 columns. We could go into the design, I just inherited the design. I'm still flabbergasted that I completely removed the code. Saved the entire package. Physically deleted the objects then ran the that specific task without any code and watched it create the objects.
February 4, 2010 at 1:15 pm
Not saying this is it, but have you used Expressions in SSIS? If not, it's *possible* that an expression is either i) changing the variable (assuming script is in variable) or ii) changing the text itself in the ExecSQL task.
To prove to yourself that you're not crazy, simply runa trace on the target DB while you execute the pkg - this will show you *exactly* what's being called by SSIS. If it's truly sending the 'old' code, then something in the pkg (expressions, config etc etc) is over-riding what you have set.
HTH,
Steve.
February 4, 2010 at 1:55 pm
Steve, thanks. Believe it or not, I can't run a trace on the target DB. I don't have that level of access. That was something I tried and found out I don't have priviledges to run traces/look at logs. Nice.
I did drop the task and recreate it.
I also on the create table statement I added an 's' to the name and ran it twice. That did cause a failure of the task. I was able to verify that code was being processed. I guess I'll print it out and get the yellow highlighter out. After that, it's punch cards...:w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply