SSIS OLE DB Error

here is your error:
"Cannot insert the value NULL into column 'ReportPeriod', table

make sure you either make that column in the table nullable, or wrap an isnull() around it in your query to pull the data.

dear user,
The column in your destination 'ReportPeriod' doesnt accept null...
You are triyng to insert null...
change the package or change the column
Helped?
regards,
pedro

Thanks guys

I am sorry I typed the wrong Error Messages. Below are the error messages that I get for most of the packages..... Please Help

[MN [76]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[MN [76]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[SSIS.Pipeline] Error: component "MN" (76) failed the pre-execute phase and returned error code 0xC0202025.

PLease help me

in you connection managers, change the provider of your SQL connection.
the package was made in SQL 2005, and you are uding now in SQL 2008, correct?
regards
Pedro

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/763decbe-92a1-4942-b536-1f392d273eb1/

No Pedro, The package was actually built in SQL 2000 and I am trying to rebuild a new package using SSIS in SQL 2008

ok... update conenctoion with provider 10.0 (SQL 2008) as I told before  and give feedback

Hi Chap, Thanks for the Link I guess it will solve my Problem Now I have a NTEXT field at the source and Dt_STR at the destination. but the conversion Between those two datatypes is not supported the only workaround is converting Ntext To text Field as far as I know and I am getting these errors when I do that ..... So Please let me know how to convert Ntext to Str

thanks

use data conversion of dataflow

It is using sql native client 10.0 and data conversion between Ntext and STR is not supported that is why i am forced to convert it to Text which I guess is giving me these Errors...

can you convert the data to nvarchar(max)?

you mean change it to nvarchar (max) in my Database...... No I cant change it...

HI Danny,
I see your problem...
Check this link:
https://www.experts-exchange.com/questions/23825542/unicode-and-non-unicode-string.html
helped?
Regards,
Pedro

The issue has been solved ... Thanks to Chap I converted the varchar to nvachar and issue is resolved

Hi
Please help me solve these other errors

I am using data conversion in between and transforming data from Oracle to Sql server but Ntext to DT_14 or dbtimestamp is not supported which might be the cause of these errors. Is there any way to solve this issue....

[OLE DB Destination [211]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [211]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (224)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (224)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (211) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (224). 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.


Thanks

which version of SQL you have?
Maybe you need to change your connection in connection manager to
Microsoft SQL Server Native Client 10.0 (SQL 2008)
Regards,
pedro

i am using sql native client 10.0 only......

re-create your connection in connection manager, and give feedback!
regards,
pedro

i did but not luck..... I have no idea why is it giving me that error.............

'Ntext to DT_14 or dbtimestamp is not supported' - can you find that in your logs somewhere?
conversion from strings to dates is always a problem.
You could use Oracle TO_CHAR or TO_DATE in your source query to convert it beforehand. Then the data type will already appear as some form of date in SSIS.

I tried to change the source and transfered all the data into excel sheet which is my source now and trying to map it to the destination Sql Server 2008............ I still get the same errors....... I don know what is the problem.... Please Help me...

Can you tell me where you saw the 'Ntext to DT_14 or dbtimestamp is not supported' error? We're trying to work out what the exact error is and we'll need an error message. The last one didn't have enough detail.
I suggest you start from a brand new package and don't bother with Excel, it'll add to the confusion.

when Im trying to use a data conversion component and convert Ntext to STR it is showing me a cross mark saying that it does not support such conversion.

Hi Bhavesh
 
I have attached a excel sheet which contains data that has been retrieved from my oracle source and I am also enclosing the sql cmd to create a table (copy of my SQL Table).... Try to insert data from excel to SQL Table ........ I still get the same error.. Please Take a look and let me know if i am doing anything wrng here.....
 
Excel contains 2 sheets IPCallManager and sheet1 which replicate my source and destination respectively
 
CREATE
TABLE [dbo].[CallManager](
[IPTelephonyCallManagerDataId] [int]
IDENTITY(1,1) NOT NULL,
[CallManager] [nvarchar]
(20) NOT NULL,
[DevicePartition] [nvarchar]
(129) NULL,
[DevicePool] [nvarchar]
(129) NULL,
[Model] [nvarchar]
(125) NULL,
[DeviceCount] [int]
NULL,
[DluCount] [int]
NULL,
[GoodCallPercentage] [numeric]
(6, 2) NULL,
[MOSAverage] [numeric]
(6, 2) NULL,
[ReportPeriod] [int]
NOT NULL,
[DataEntryDate] [datetime]
NOT NULL
)
ON [PRIMARY]
SQL statement that I am using before the Dataflow task to get the ReportPeriod and entryDate Fields
DECLARE @ReportPeriod INT, @ReportPeriodLM INT
SET @ReportPeriod = (YEAR(GETDATE())*100) + MONTH(GETDATE())
SET @ReportPeriodLM = (YEAR(DATEADD(M,-1,GETDATE()))*100) + MONTH(DATEADD(M,-1,GETDATE()))
IF DAY(GETDATE())=1 BEGIN
    DELETE FROM dbo.IPTelephonyCallManagerData WHERE ReportPeriod=@ReportPeriodLM
    DELETE FROM dbo.IPTelephonyCallManagerLicense WHERE ReportPeriod=@ReportPeriodLM
    DELETE FROM dbo.IPTelephonyCallManagerQualityData WHERE ReportPeriod=@ReportPeriodLM
END
IF DAY(GETDATE())>1 BEGIN
    DELETE FROM dbo.IPTelephonyCallManagerData WHERE ReportPeriod=@ReportPeriod
    DELETE FROM dbo.IPTelephonyCallManagerLicense WHERE ReportPeriod=@ReportPeriod
    DELETE FROM dbo.IPTelephonyCallManagerQualityData WHERE ReportPeriod=@ReportPeriod
END
 
Let  me know where am i going wrong
 
Thanks
CallManager---Copy.xls

Hi guys
[OLE DB Destination [211]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [211]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (224)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (224)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (211) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (224). 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.

Please help me ......... I am new to ssis I so not know the exact reason for the error but problem might be bcz its unable to convert Ntext to int and the two columns ReportPeriod and DateEntry do not accept Nulls and they do not get any data from source .... In DTS we used the folowing code to fill the data in those columns

DECLARE @ReportPeriod INT, @ReportPeriodLM INT
SET @ReportPeriod = (YEAR(GETDATE())*100) + MONTH(GETDATE())
SET @ReportPeriodLM = (YEAR(DATEADD(M,-1,GETDATE()))*100) + MONTH(DATEADD(M,-1,GETDATE()))
IF DAY(GETDATE())=1 BEGIN
    DELETE FROM dbo.IPTelephonyCallManagerData WHERE ReportPeriod=@ReportPeriodLM
    DELETE FROM dbo.IPTelephonyCallManagerLicense WHERE ReportPeriod=@ReportPeriodLM
    DELETE FROM dbo.IPTelephonyCallManagerQualityData WHERE ReportPeriod=@ReportPeriodLM
END
IF DAY(GETDATE())>1 BEGIN
    DELETE FROM dbo.IPTelephonyCallManagerData WHERE ReportPeriod=@ReportPeriod
    DELETE FROM dbo.IPTelephonyCallManagerLicense WHERE ReportPeriod=@ReportPeriod
    DELETE FROM dbo.IPTelephonyCallManagerQualityData WHERE ReportPeriod=@ReportPeriod
END
 
I have attached a excel sheet which contains data that has been retrieved from my oracle source and I am also enclosing the sql cmd to create a table (copy of my SQL Table).... Try to insert data from excel to SQL Table ........ I still get the same error.. Please Take a look and let me know if i am doing anything wrng here.....
 
Excel contains 2 sheets IPCallManager and sheet1 which replicate my source and destination respectively
 
CREATE
TABLE [dbo].[CallManager](
[IPTelephonyCallManagerDataId] [int]
IDENTITY(1,1) NOT NULL,
[CallManager] [nvarchar]
(20) NOT NULL,
[DevicePartition] [nvarchar]
(129) NULL,
[DevicePool] [nvarchar]
(129) NULL,
[Model] [nvarchar]
(125) NULL,
[DeviceCount] [int]
NULL,
[DluCount] [int]
NULL,
[GoodCallPercentage] [numeric]
(6, 2) NULL,
[MOSAverage] [numeric]
(6, 2) NULL,
[ReportPeriod] [int]
NOT NULL,
[DataEntryDate] [datetime]
NOT NULL
)
ON [PRIMARY]
 
Let  me know where am i going wrong
 
Thanks
CallManager---Copy.xls

Do you really want to involve Excel in this integration? Did you try casting the data before it hits SSIS?