Unable to perform data load by using SQL Server Import export wizard

Question

I am trying to export the data form one server to another server. I have used custom SQL to fetch data and created a table at destination:

SELECT [Vantive OrgID]= end_user_vantive_id, [City]= end_user_city,
[State]= end_user_state, [Postal]= end_user_postal_code, [Country] = end_user_country,
[Area]= st_area, [Region]= st_region, [Territory]= st_territory,
[Geo]= st_geo, [Sales Order Number]= sales_order_number,
[Date]= order_date, [Year]= year_num, [Month]= month_num,
[Quarter]= datepart(q,order_date),
FROM scaall.vf_bookings WITH (NOLOCK)
WHERE ISNUMERIC(sales_order_number) = 1 and order_date >= '1/1/15';

Very few data got copied and thrown below error message.

Copying to [dbo].[SCASalesOrderExport_20180907] (Error) Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Query returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

It seems you forgot to include that Custom SQL. We can't debug code without the code! 😁 — Larnu
from the error it sounds like there was maybe a network or other environmental error. Have you tried to re-run it? — ADyson
perhaps there's a timeout, or disk error or something. It's really impossible for us to know exactly just from the error message. You will have to investigate you environment. We can't do that for you. google.co.uk/… may give you some starting points. — ADyson

Andrea Andrea · Accepted Answer · 2018-09-07T10:25:56

Instead of using SQL Server Import and Export Wizard you can try with bcp utility (more info here).

bcp is a command line utility that was created for this very purpose: efficiently transfer data between different databases.

EXPORT DATA WITH A QUERY

bcp "select * from [SRC_DB_NAME].[SCHEMA_NAME].[TABLE_NAME]" queryout c:\data.bcp -c -T -U<login_id> -P<password> -S<src_server_name\instance_name>

IMPORT DATA

bcp [DEST_DB_NAME].[SCHEMA_NAME].[TABLE_NAME] in c:\data.bcp -c -T -U<login_id> -P<password> -S<dest_server_name\instance_name>

Unable to perform data load by using SQL Server Import export wizard

1 Answers