December 3, 2013 at 4:20 pm
Hi,
At the moment Im trying to make package to update my tables.
When I open my OLE DB Source I get the first error look picture 1
But I get the following errors;
SSIS package "C:\Visual Studio Projecten\Integration Services Project1\Integration Services Project1\Incident categorie Nt St Change.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x800470C8 at Data Flow Task, OLE DB Destination [55]: The external columns for OLE DB Destination are out of synchronization with the data source columns. The external column "incident_ref" needs to be updated.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x800470C8 at Data Flow Task, OLE DB Destination [55]: The external columns for OLE DB Destination are out of synchronization with the data source columns. The external column "incident_ref" needs to be updated.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "inc_cat_sc" (184) on output "OLE DB Source Output" (163) and component "OLE DB Source" (152) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "inc_cat_n" (185) on output "OLE DB Source Output" (163) and component "OLE DB Source" (152) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [55]: 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: "Invalid character value for cast specification".
Error: 0xC020901C at Data Flow Task, OLE DB Destination [55]: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[Copy of incident_ref] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [55]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" 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.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (55) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (68). 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.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Sort" (278) failed with error code 0xC0047020 while processing input "Sort Input" (281). 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.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Incident categorie Nt St Change: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Visual Studio Projecten\Integration Services Project1\Integration Services Project1\Incident categorie Nt St Change.dtsx" finished: Failure.
The program '[5680] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
Now Im looking at my Databases (I used a previous database (Where my package normal working) and the new database(where he dont work)
I have trying to put the old data in de new database like this syntax but i get errors to;
INSERT INTO DWH.DBO.incident (incident_id, incident_ref)
SELECT [dwh-test].dbo.incident.incident_id, [dwh-test].dbo.incident.incident_ref
from incident
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'inc_status', table 'DWH.dbo.incident'; column does not allow nulls. INSERT fails.
The statement has been terminated.
If you look in my table Incident_ref (in previous database you see the next data in the column incident_ref picture 2 and in picture 3 the new database column incident_ref
Here is my package that I work with;
Look the pictures*
OLE DB Source the following query;
SELECT [incident].incident_id, inc_status, date_logged, sectn_dept_id, [incident].inc_cat_id, inc_prior_id, callback_reqd, event_type, inc_data.total_service_time, inc_resolve_svd, callback_rmk, [incident].incident_ref, sla_id, Assystreplication.dbo.incident.item_id, inc_resolve_due, incident.inc_resolve_act, inc_close_date, bldng_room_id, inc_data.u_date1, inc_resolve_usr, inc_cat_sc, inc_cat_n, cor_sla_achieved_id, usr_ref, aff_usr_id, assyst_usr_id, ass_usr_id, serv_dept_id, ass_svd_id, incident.descrip_grp_id, inc_desc1, inc_desc2, inc_desc3, inc_desc4, incident.project_id, cause_id, cause_item_id
FROM Assystreplication.[dbo].[inc_data]
join Assystreplication.[dbo].[incident]
on [inc_data].incident_id = [incident].incident_id
join DWH.[dbo].inc_cat
on Assystreplication.dbo.[incident].inc_cat_id = DWH.dbo.inc_cat.inc_cat_id
join DWH.[dbo].item
on Assystreplication.[dbo].[incident].item_id = DWH.[dbo].item.item_id
join DWH.[dbo].product
on DWH.[dbo].item.product_id = DWH.[dbo].product.product_id
full outer join DWH.dbo.correctie_incident
on Assystreplication.dbo.incident.incident_id = DWH.dbo.correctie_incident.incident_id
where ((inc_cat_sc like 'rfc%' and product_sc not like 'RFC S%')
and inc_cat_sc not like 'rfc-st%'
or inc_cat_sc like 'IA C%'
or inc_cat_sc like 'BC%'
or inc_cat_sc like 'RLS%'
or (inc_cat_sc like 'rfc-ma%' and product_sc not like 'RFC S%')
or (inc_cat_sc like 'st%' and product_sc not like 'RFC ST%'))
and event_type = 'c'
Data conversion
Devired Column
Devired Column
SORT
Destination
See the pictures;
Why it dont works....
December 7, 2013 at 4:35 am
Hi Karmi
Could you show as CREATE TABLE statement for both source & dest. tables.
I'm afraid you are unable to insert data due to nonnulable column deifinition (inc_status) in DWH.DBO.incident.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new row
without adding a value to this field.
Br.
Mike
December 9, 2013 at 4:11 am
What's the status on this, did you resolve your issues?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply