I have a spreadsheet. It contains company information such as name, address, phone, email, etc. All I want to do is a flat import into SQL into a table it can create so I can do some processing on it. I'm just using MS SQL Import Wizard. I'm not using BCP.
It gets all the way to the end and gives me this error:
Blockquote Operation stopped...
Initializing Data Flow Task (Success)
Initializing Connections (Success)
Setting SQL Command (Success)
Setting Source Connection (Success)
Setting Destination Connection (Success)
Validating (Success) Messages Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. (SQL Server Import and Export Wizard)
Prepare for Execute (Success)
Pre-execute (Success)
Executing (Error) Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - crm_company$.Outputs[Excel Source Output].Columns[Directions] on Source - crm_company$.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - crm_company$.Outputs[Excel Source Output].Columns[Directions]" failed because truncation occurred, and the truncation row disposition on "Source - crm_company$.Outputs[Excel Source Output].Columns[Directions]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
Copying to [dbo].[crm_company] (Stopped) Messages Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - crm_company$ returned error code 0xC020902A. 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)
Post-execute (Success) Messages Information 0x4004300b: Data Flow Task 1: "Destination - crm_company" wrote 469 rows. (SQL Server Import and Export Wizard)
Blockquote
First of all, I have told DTS to ignore any truncation errors. The source fields are either int or nvarchar. I edit the mappings and force it to be int instead of float for the required columns. I have set the default column size to 510 from 255. For 2 columns, Directions and Notes, it has decided that the Directions is nvarchar and the Notes is nvarchar(max). I override the Directions one so that it is treated as nvarchar(max) and I can see that in the table it creates after it runs. However, when it runs it fails EVERY TIME with the above error.
I just want this data into a table. That's all. If I specify a table manually first and import, it still errors. The longest text in Directions is 978 characters long so it is hardly huge. I don't care if it truncates, I just don't want it to stop when it feels it needs to. There are 16000 rows importing and only 470 get imported before it fails.
I don't know which row fails it in the source file because strangely, DTS is not pulling the data in, in the order it is in, in the spreadsheet. Go figure. I tried overriding DTS's stupid attemtps at deciding what the column formats are by sticking 20 rows of text data in, but that still fails.
I tried many things to get around this and found it alot easier to import the spreadsheet into Access and then import Access to SQL. Job done in 5 minutes.