Are you using lookup's or other transformations that are caching data? Take a look at your output window to see what the package is doing right before it hangs up....

Did you check to see if you can see SQL Server Integration Services in the SQL Server Configuration Manager? If you can see the service, make sure it is running....

If your source data is small (1 - 20 million records), then you can do full extractions nightly. Otherwise, you will have to find a way to identify data changes....

Datatype incompatibilities are typically the problem here. I have had problems importing dates sometimes because Oracle Datetime datetype can handle dates outside the range that SQL Server DateTime datatype can...

Wouldn't you just change the collation using the ALTER DATABASE statement since this seems to be a one time operation??

Here is a MSDN reference for changing collations...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_1pwz.asp

I use this code when I need to use a UDL file to instantiate a connection...

 Dim oConn, sUDLFile

sUDLFile =  DTSGlobalVariables("gsPubsUDL").Value

Set oConn =...