June 5, 2009 at 7:42 am
I was attempting to use a global temp table to dump in data then a doing a update/join statement on a physical table to update the data, then dropping the global temp table when I'm done.
Execute SQL Task 1
-Drop ##mytemp if it exist on Server A
Execute SQL Task 2
-Create ##mytemp on Server A
Dataflow Task
-Select statement on Server B
-Insert into ##mytemp on Server A
Execute SQL Task 3
-update statement
Execute SQL Task 4
-drop temp table
It seems to be 50/50 as to whether the dataflow task succeeds or fails. The reported error is always ##mytemp does not exist. I have retain connection set to true (even though that should matter as global temp table persists across all connections). I have delay validation set to true (it always runs steps 1 and 2 without fail)
Any clues?
June 5, 2009 at 7:52 am
also be sure that you set 'validate external metadata' to false for all objects within the dataflow
June 5, 2009 at 8:06 am
Should I just create a physical table in the database and drop it when I'm done. Is that a better solution?
June 5, 2009 at 9:19 am
probably makes no difference unless you are having a very finite look at performance, but in either case you need to set validateexternalmetadata to false.
This way SSIS doesn't check for a table or hash table that may not exist at the time it is checking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply