How do I fix the multiple-step OLE DB operation errors in SSIS

Question

I m attempting to make a DTS package to transfer data between two databases on the same server and I m getting the following errors  Iv read that the Multiple-step OLE DB operation generated error can occur when you are transferring between different database types and there is loss of precision  but this is not that case here  How do I examine the column meta data      Error  0xC0202009 at Data Flow Task    piTech  183   An OLE DB error has   occurred  Error code  0x80040E21  An   OLE DB record is available   Source     Microsoft SQL Native Client     Hresult  0x80040E21  Description     Multiple-step OLE DB operation   generated errors  Check each OLE DB   status value  if available  No work   was done         Error  0xC0202025 at Data Flow Task    piTech  183   Cannot create an OLE DB   accessor  Verify that the column   metadata is valid       Error  0xC004701A at Data Flow Task    DTS Pipeline  component  piTech   183    failed the pre-execute phase and   returned error code 0xC0202025

User · Answer

This query should identify columns that are potential problems     SELECT    FROM  source  INFORMATION SCHEMA COLUMNS src     INNER JOIN  dest  INFORMATION SCHEMA COLUMNS dst          ON dst COLUMN NAME   src COLUMN NAME WHERE dst CHARACTER MAXIMUM LENGTH  lt  src CHARACTER MAXIMUM LENGTH

User · Answer

-2147217887  message  IDispatch error  3105  source  Microsoft OLE DB Service Components  description  Multiple-step OLE DB operation generated errors  Check each OLE DB status value  if available  No work was done      This is what I was also facing  The problem came from the fact that I changed my SQLOLEDB 1 provider to SQLNCLI11 without mentioning the compatibility mode in the connection string  When I set this DataTypeCompatibility 80  in the connection string  I got the problem solved

User · Answer

Also check if the script has no batch seperator commands  remove the  GO  statements on a single line

User · Answer

This error is common when the source table contains a TEXT column and the target is anything other than a TEXT column  It can be a real time-eater if you have not encountered  or forgot   this before     Convert the text column to string and set the error condition on truncation to ignore   this will usually serve as a solution for this error

User · Answer

In my case  the problem was setting the variable of the Execute SQL Task  in parameters mapping the parameter name   in OLEDB must be the position of the parameter that you call in the stored procedure   I had 1  but the first parameter starts in 0  so I changed it and voil

User · Answer

This error will also occur when trying to do an insert and a field is coded not null and nulls are trying to be inserted

User · Answer

Take a look at the fields s proprieties  type  length  default value  etc    they should be the same   I had this problem with SQL Server 2008 R2 because the fields s length are not equal

User · Answer

I had a similar issue when i was transferring data from an old database to a new database  I got the error above  I then ran the following script  SELECT   FROM  source  INFORMATION SCHEMA COLUMNS src INNER JOIN  dest  INFORMATION SCHEMA COLUMNS dst ON dst COLUMN NAME   src COLUMN NAME WHERE dst CHARACTER MAXIMUM LENGTH  lt  src CHARACTER MAXIMUM LENGTH  and found that my columns where slightly different in terms of character sizes etc  I then tried to alter the table to the new table structure which did not work  I then transferred the data from the old database into Excel and imported the data from excel to the new DB which worked 100

User · Answer

For me the answer was that I was passing two parameters to and execute SQL task  but only using one  I was doing some testing and commented out a section of code using the second parameter  I neglected to remove the parameter mapping   So ensure you are passing in the correct number of parameters in the parameter mapping if you are using the Execute SQL task

User · Answer

I hade this error when transfering a csv to mssql I converted the columns to DT NTEXT and some columns on mssql where set to nvarchar 255    setting them to nvarchar max  resolved it

User · Answer

You can use SELECT   FROM INFORMATION SCHEMA COLUMNS but I suspect you created the destination database from a script of the source database so it is very likely that they columns will be the same   Some comparisons might bring something up though   These sorts of errors sometimes come from trying to insert too much data into varchar columns too

User · Answer

This issue will come mostly due to empty rows at the end of the file  remove those and run the job

[sql-server] How do I fix the multiple-step OLE DB operation errors in SSIS?

Examples related to sql-server

Examples related to ssis