Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS problem with my table or


SSIS problem with my table or

Author
Message
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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....
Attachments
picture2.JPG (22 views, 22.00 KB)
picture1.JPG (21 views, 42.00 KB)
Data conversion.JPG (13 views, 94.00 KB)
OLE DB Destination Editor.JPG (13 views, 63.00 KB)
sort.JPG (9 views, 69.00 KB)
Derived Column.JPG (8 views, 92.00 KB)
Derived Column2.JPG (7 views, 72.00 KB)
michal.lisinski
michal.lisinski
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 1090
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16439 Visits: 13202
What's the status on this, did you resolve your issues?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search