January 31, 2007 at 12:50 pm
I have an SSIS package that is supposed to collect data from several customer databases spread across several servers. The package is not yet in production.
When running the package in QA using DTEXEC, the package is failing with the error message:
Error: 2007-01-31 11:01:20.53
Code: 0xC0202009
Source: ...Billing Connection manager "Customer_DB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in opening server connection".
What would be the best way to modify the package to tell me the specifics about the connection that failed? In particular the server and database name?
One tool that would be useful is if someone has a tool that would cycle through the user variables and print the names and values to the output messages. It would be real slick if I could have SSIS automagically dump the variables when an error occurs.
So, first question, is there a way to setup a connection manager to tell me the settings it is using when it fails?
February 1, 2007 at 11:59 am
You could set up an OnError Handler at the package level and set up an SMTP connection and send an email on the package failure. When you say "setup a connection manager to tell me the settings it is using when it fails", what exactly are you referring to?
can you elaborate a little more on structure of the DTSX package?
February 5, 2007 at 9:54 am
A brief overview of the application.
The application was designed so each customer has their own database. There are many customers (a good thing!), with each production server supporting several dozen to a few hundred databases. Several database servers are used. We also have several environments. Some used for development (multiple), testing (multiple), and one for production. There is a separate billing system, and it has its own suite of servers and environments.
A brief overview of the SSIS package.
The SSIS package gathers data from each of the customer databases into a staging database, manipulates the data there a bit, then forwards it on to the billing system.
For each environment we have a table to tell us which servers are used for each role. And on the database servers we have a mechanism to provide a list of the customer databases on that server. Because this infrastructure was already in place, I chose to use it instead of using the SSIS configurations feature.
An overview of the package.
1) A SQL task is used to get the servers for this environment
2) A SQL task to get the list of database servers for this environment.
3) A ForEach loop to cycle through the database servers.
3.1) A SQL task to get the list of customer databases on this database server.
3.2) A ForEach loop to cycle through the databases on a server.
3.2.1) A SQL task to identify what dates need to be billed on this database.
3.2.2) A ForEach loop to cycle through the dates on this database.
3.2.2.1) A sequence container containing two data flow tasks.
3.2.2.1.1 A task containing six pairs of OLE DB Sources and OLE DB Destinations. Each pair gets the data for one billable metric from the customer database and passes it to the staging point
3.2.2.1.2 A task containing four pairs of OLE DB Sources and OLE DB Destiantions. Each pair gets the data for one billible metric for a customer from a work database and passes it to the staging point.
3.2.3) Once the data for a customer database is collected, the customer database is marked as staged.
4) A SQL Task is used to aggregate the staged data and forward it to the billing system.
The package uses variables to identify the servers and databases are being processed. The Expressions feature of the Connection Manager is used to update these properties.
The OnError Handler looks promising. However, I haven't figure out where to hook a Connection Manager error, which seems to be the component that is failing. I can also write a SQL Script to manually print the name and value of each variable, but would really like to switch that over to a dynamic method that cycles through all the variables. I'll continue to work on the OnError handler.
When running the package in a test environment, the package sees to hang. Since my earlier posting I've learned it appears to be waiting for something to time-out. I found if I switched the initial set server names from the development values to 'localhost', the long delay seems to go away. It behaves like it is trying to validate the connections before the server names have been set, even though the DelayValidation on each connection manager is set to 'True'.
February 5, 2007 at 10:11 am
Regarding your second question, the connection manager settings I am refering to are the server name and database name properties. Knowing these values would help me to figure out if the error is related to the package being coded incorrectly (for example, the staging database is mapped to the wrong server in the environment), or if SSIS is behaving differently than expected. When the error message says the Customer database connection is failing, I don't know which customer database it is referring to. Knowing that would help me investigate the customer database to determine if the problem is related to the new SSIS package, or perhaps something unique about the customer database itself.
February 8, 2007 at 2:14 pm
Hm, I'm not sure then.. I see what you're saying, but I don't really have a suggestion on how to get that value dynamically... Sorry I can't be of more help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply