December 20, 2007 at 7:03 am
I am dealing with a huge database with millions of records, now as part of the ETL process I am trying to extract data from this oracle source into an SQL Server 2005 staging area and then insert the data again to an oracle destination.
I have an SSIS package with a number of DFT(s) each consisting of a source, transformation and destination. The transformation is purely a row count. I also have a send mail task that sends some statistics on success about each DFT.
So I have 9 DFT's and 9 send mail tasks, however, I have 2 problems:-
(1) Oracle sessions limitations - I am limited on the no of sessions that I can connect with to oracle and the query I want to run can take hours.
(2) I have a query
Select Distinct Substr(BOO, -3,3) AS FOO From TABLE1
WHERE 1=2
however, I always a "cannot convert between unicode and non-unicode string data types" error on the source. The fields that I am dealing with in our oracle database is varchar2 (which supports unicode) and even though I introduce a data transformation task to convert to unicode, I get the same error on the source! My hunch is that SSIS is not comfortable with the query or an explicit cast maybe needed even though when I parse it, the result is positive.
Any ideas???
December 26, 2007 at 5:50 am
This is very weired, however, without me doing anything other than switching from SQL query view to table view and back and then clicking on the mappings tab and it worked! HOW, I really don't know!!!
December 26, 2007 at 9:07 am
What is the language for which unicode is used for
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 26, 2007 at 9:28 am
Right click on the OLE DB data source and hit Show Advanced Editor. Look at the Input and Output Properties tab. Expand the External Columns. The data type can be change there. It was probably DT_WSTR when you started the process, but by performing the changes that you made it probably switched it to DT_STR.
Norman
DTS Package Search
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply