error running ssis package

kubyustus

I run an ssis task with right click in the BIDS.

the task transform data of the tables between sql2005 DB to another sql2005 DB.

this is the error I get:

Code Snippet

An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description:

"TCP Provider: The semaphore timeout period has expired.

".

helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Task failed: CopyTables2

i have a similar task, only diffrent tables that being transfered, same settings. this task run succesfully.

for the task that fails it takes a long time to run (something like half an hour, maybe more) and then it falils. the task that succeeds finish fast (something like 5 minutes). the data in the tables that being transeferred in the good task is much smaller than the data in the tables in the failed task.

Anyone knows the reason to the error.

Thanks.

jwelch

It sounds like a timeout issue. You say that the task which is failing is moving a large amount of data, and taking more than 30 minutes Is it always failing after the same amount of time If so, you might check the timeout on the connections.

kubyustus

it looks like a timeout issue also to me. when i remove a very big table from this package (a 40 milion records table) the package run faster and finish successfully. how can i set the timeout of the connection in the BIDS. i didn't see timeout property in the connections manager.

anyway, i dont understand this SSIS. in sql2000 i transferred very large tables with no problem using the dts. since i upgraded to sql2005 i encounter lots of bugs in the SSIS.

Thanks.

Anthony Martin

Open up your connection manager, then click on the 'All' page, under the 'Initialization' category there is a 'connect timeout' and 'general timeout' properties. It seems like by default these are both set to 0, which I would interpret as unlimited though.

Could you give a more detailed explanation of your package. Are you transferring the data through a stored procedure in an execute sql task or a data flow task

jwelch

You might also check the SQL Server to make sure the query governor is not enabled. The setting is located in the Server Properties, under the Connections page.

kubyustus

Thanks for your answer.

I'm sorry, i can't find the 'all' page and i can't find the 'initialization' catrgory in the connections manager.

I created a task in the control flow in the BIDS. i dragged from the toolbox to the control flow, transfer sql server object. i configured the source DB and destination DB and the tables I want to transfer. I only transder the data in the tables.

appreciate your help.

kubyustus

The query governor is indeed not enabled. thx.

jwelch

When you open the connection manager, you should see Connection and All listed on the left hand side of the dialog box. Click All to see all the information realted to the connection.

kubyustus

Thanks for your answer.

i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers.

I see there 2 connections: one of the source DB and the other is the destination DB.

when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention.

where can i find it

Sorry for all the questions...

Thanks.

AVNIP

kubyustus wrote:

Thanks for your answer. i don't see it. Under the control flow and data flow area in the BIDS there is the connection managers. I see there 2 connections: one of the source DB and the other is the destination DB. when i click on each one of them i get the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. other than that i don't see the 'all' button than u mention. where can i find it Sorry for all the questions... Thanks.

Hi,

Please double-click the icon of the "Connection" in the "Connection Managers" tab.

You get a window called "Connection Manager".

Here you must have a left pane where you see "Connection" which should be highlighted.

In the main portion of the window you see the "Server Name", "Log on to the server" (for Authentication) and "Connect to a database".

On the left pane, just below "Connection" you should be able to find a tab called "All". Click on this tab and the main portion of the window should now show you properties like "Advanced", "Initialization", "Named ConnectionString", "Pooling", "Security", etc.

Please check the value against "Initialization" > "Connect Timeout".

Thanks and Regards,

B@ns

kubyustus

in the BIDS:

i double click the icon of the "Connection" and got the SMO connection manager editor dialog box, there i can configure the connection of the source or destination DB. i didn't find the window called "Connection Manager".

where is it

jwelch

OK, it finally clicked. You are using the Transfer SQL Server Objects task. Try using a data flow with an OLEDB Source and OLEDB Destination instead.

kubyustus

yes, I'm using the sql server object task.

I try now the data flow oleDB source and destination.

now when I double click the connection in the connection manager i indeed see the 'ALL' in the left side.

the value 0 is what I need for the connection and general timeout property, right

However, I'm not so familiar with this way of transfering data. i dragged the oleDB source and oleDB destination from the toolbox. i configured the connections but i see there an option to select only one table to transfer. i need to transfer data of many tables.

in the oleDB source editor dialog window i have: oleDB connection manager, then data access mode where i can choose table of view, sql command or sql command from variable and then i can choose table or view, but only one.

How can I transfer many tables

Thanks alot for your answers.

jwelch

You'd have to create more data flows with their own source and destination. I'd recommend that you use the SQL Server Transfer Objects task to move your smaller tables, and use the data flow tasks to move only the large tables.

kubyustus

Thanks, I will try it and let u know.