CODEWITHSUNDEEP
ssisbids
Shoeb
Shoeb

Reputation: 652

Import/Export wizard fails on export to Excel

I am exporting a table from sql server to Excel using the SSIS Import/Export wizard. In Proceed step, I get a warning icon in each record regarding conversion of types. If I ignore then the export fails after clicking Finish in the last screen.

I search in Stack Overflow regarding this but unable to get relevant answer...

SQL table fields and their type are below:

CREATE SomeTable
(
    EmpId numeric(9)
,   Name varchar(50)
,   Address varchar(50)
,   ContactNo varchar(50)
);

Error that comes is written below

Copying to tblSSISFlatImport (Error) Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (39)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (39)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - tblSSISFlatImport" (28) failed with error code 0xC0209029 while processing input "Destination Input" (39). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Upvotes: 2

Views: 5220

Answers (1)

user1896267
user1896267

Reputation:

I've seen this error before and it could be a result of several things.

1) Make certain that in the Data Conversion Transformation Editor that the fields Name, Address and ContactNo have a Data Type set to "Unicode string [DT_WSTR] with a length of 50 in order to convert from SQL Server to Excel.

2) if you are working on server, make certain that Run64BitRuntime is set to True (otherwise for set it to False). This you can check by right-clicking on the package name (first item under Solution Explorer on the right side of the window), selecting properties and then Debugging under Configuration Properites.

3) Under the Data Flow tab, double-click the source OLE DB item, select Connection Manager and verify that the values are being pulled by selecting Preview at the bottom. It may be possible that you are not pulling the data correctly and that is throwing off the conversion step.

4) Lastly, compare the settings between the Data Conversion Input vs. Data Conversion Output. This is done by selecting the Data Conversion task and selecting the hyperlink for "Show Advanced Editor" under Properties. When the window opens, select the tab "Input and Output Properties." In the left pane, expand the Input columns and Output column folders to view the three fields. By selecting on a field you can view the properites. Make certain that the lengths are correct and that the Data Type Properties are defined correctly. As mentioned before, the output for each should be "Unicoce string [DT_WSTR]" with a length of 50.

Hope this helps.

Upvotes: 2

Related Questions