RoboRubik
asked on
DTS - Query timeout expired in Transform Data Task Properties
I am trying to run a query on a remote server and load the results into a table on the local server.
In the DTS Designer I have created the source and destination connections. I have created the query, which works great in Query Analyser. It is about 300 lines long and takes about 100 seconds to run.
I then create a Transform Data Task and in the Transform Data Task Properties window, I browse to the file with my query. This is where my trouble begins.
If I go to the Transformations tab, the source columns area is empty, indicating that it could not figure out what the dataset returned by my query looks like. So, back on the Source tab I click the preview button below the SQL Query text box hoping to populate the source columns... it pops up an empty window titled 'View Data' and the hour-glass goes for about 30 to 45 seconds and then it returns the error: "No rowset was returned from the SQL statement." Of course this is true, because the remote server will not be returning data for nearly another minute.
So then I try the Build Query button. In the Data Transformation Services Query Designer, I run the query and it returns "Timeout Expired" after about 30 seconds.
I checked the remote connection's properties and confirmed the General Timout is set to "0" (I've also tried 300 & 3000). I've looked for a general timeout setting for Enterprise Manager, but can find nothing that effects the length of time it will wait to test the query. I've also tried using a Data Driven Query Task, but it behaves exactly the same there.
Does anyone know how I can get this dialog box to wait longer for my query to finish? Or is there another way to accomplish my goal?
In the DTS Designer I have created the source and destination connections. I have created the query, which works great in Query Analyser. It is about 300 lines long and takes about 100 seconds to run.
I then create a Transform Data Task and in the Transform Data Task Properties window, I browse to the file with my query. This is where my trouble begins.
If I go to the Transformations tab, the source columns area is empty, indicating that it could not figure out what the dataset returned by my query looks like. So, back on the Source tab I click the preview button below the SQL Query text box hoping to populate the source columns... it pops up an empty window titled 'View Data' and the hour-glass goes for about 30 to 45 seconds and then it returns the error: "No rowset was returned from the SQL statement." Of course this is true, because the remote server will not be returning data for nearly another minute.
So then I try the Build Query button. In the Data Transformation Services Query Designer, I run the query and it returns "Timeout Expired" after about 30 seconds.
I checked the remote connection's properties and confirmed the General Timout is set to "0" (I've also tried 300 & 3000). I've looked for a general timeout setting for Enterprise Manager, but can find nothing that effects the length of time it will wait to test the query. I've also tried using a Data Driven Query Task, but it behaves exactly the same there.
Does anyone know how I can get this dialog box to wait longer for my query to finish? Or is there another way to accomplish my goal?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER