SSIS Newbie...Need Help With Package!

[sconti11]

Ok...so I am a newbie to SSIS..I used to use DTS way back when, so I thought this would be a no issue.

I attempted to create the Control Flow, which include SQL Tasks to both delete the data from the destination tables (yes, the customer wants to remove old data from the tables each time), and then also do some update statements on the Source data to prepare it for the move. This is working. It's when I get to the Data Flow, when I encounter issues.

I have simple OLDB data source and destination controls. But when I attempt to execute I get errors on a couple of the tables. What I do not understand, is that if I do a simple INSERT statement within SQL Server, I get no errors.

The errors are the following:

[Destination 3 - PYMNT [291]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 5 - WRK_ORDER" (432) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput().


[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 3 - PYMNT" (291) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.

Any assistance will be welcome!!!

 

[SQLBill]

I've never seen the second two, but for the first one take a look at what data you are inputting to PYMNT and what the column values/lengths are. Also, on the Data Flow tab, open up the destination and look at the mappings. Make sure you are mapping the proper columns from the source to the corresponding columns in the destination.

Start there and clearing that up may also clear up the other two issues.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[fredericofonseca]

This is common to happen if the source data has "data" that does not meet either the input column definition, the output column definition of the source table, or the destination column definition, or even a cast transform you may have between the source and destination.

If you can not manage to identify the issue on the components themselves, I would advise you to temporarily load from your source component into a sql table with all the fields defined as varchar(max) without doing any cast to see if that loads.
If it loads then your issue is on the mapping to your destination table or on invalid data on one of the fields.
To check if it is data on the fields, you can now try and do individual cast on the varchar fields into what type of data they should be until you identify the field giving you issues.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[sconti11]

I understand what you are stating, but as I mentioned I have already identified that I can load my destination tables from my source tables by using a simple INSERT command within my SQL Server...and I get no errors!

So I am puzzeled as to why the SSIS data flow will not do the same thing???

 

[SQLBill]

Did you do as I suggested? Check the mappings in SSIS, maybe you have a column or two mis-mapped. On the Data Flow tab, open the destination box and click on mappings.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[sconti11]

Yes...I have checked the mappings, but i am still getting errors????

The execution results show a variety of errors, that range from invalid characters for casting to violation of primary key constraint. But when looking at the data between both source and destination, I see no issues???

I am getting down to crunch time on my project, and i need to get this figured out...any ideas?

Also I am not sure if it makes a difference, but my dataflow contains several OLDB controls for both source and destination, since I am moving data from several tables.

 

[fredericofonseca]

you were giving a way of identifying the source of the problem.

Another one, and maybe one that will turn out to be easier for you to understand, is to create a new package with a single data flow. make that one work, and then add more flows, 1 by 1 and test the full thing each time you add a new flow.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886