I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"
These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.
e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )
These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:
-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.
This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.
I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.
I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.
http://support.microsoft.com/kb/918091#appliesto
Any ideas welcome
The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.
Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".
|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||
I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.
All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?
|||Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.
Not 100% sure why this works but it seems to.
Hope this helps
No comments:
Post a Comment