August 27, 2010 at 7:12 am
Hi SQL experts,
I am trying to create an SSIS package which gets the data from Oracle and inserts into SQL server......I am using a Derived column and the transformations of the package depends upon the VBscript(In other words the transformations which i create is done by mimicking the filters which exist in the VB file ).....There are 2 derived columns which i include in the package
i)I get the time column from the Oracle and i need to apply the following transformations in derived column(for time in SQL Server)
1) Convert the time to String
2) time.SubString(9, time.length-9)
ii)I get the date column from Oracle and i need to apply the following transformations in derived column(for date in SQL Server)
1) Convert the date to String
2) date.Substring(0,date.Length-11)
3) String.Concat(concatenate the Time and date obtained from the above 2 derived columns)
I guess i m getting some datatype errrors which executing the package......Can u help me in creating the Expression
Regards,
Pradeep
August 27, 2010 at 7:28 am
You guess you're getting datatype errors? You're not sure?
You're asking us to guess what those errors might be?
You need to tell us what happens when you run the package. What errors do you get? What are you seeing that is correct?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 27, 2010 at 3:10 pm
I am pretty sure its the issue with the data type.......Can you help me in creating the Expression for the derived columns???
Thanks,
Pradeep
August 28, 2010 at 11:36 am
What are the errors that you are getting? Can you copy-paste the exact error messages?
And even more important: what are the requirements of your SSIS package? What are you trying to achieve?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 1, 2010 at 1:52 pm
Here are some of the error message when the package was run
Error: Columns "Derived Column T_TIME" AND "T_TIME" cannot convert between unicode and non-unicode string data types.
(Here the name of the Derived Column and the name of the column for the time which i get from Oracle is same(i.e T_TIME)(I specified the Derived Column returns a datatype of Unicode string[DT_WSTR])
Error: "component "OLE DB Destination" (520)" failed validation and returned validation status "VS_ISBROKEN".
I am pretty sure there is an error in Expression.....
SUBSTRING(T_TIME,9,(LEN(T_TIME)-9))
Regards,
Pradeep
September 6, 2010 at 3:38 am
I too have had problem with converting strings to datetime fields. After trying various different methods I now use a VB transform script and the DateTime.ParseExact .Net method. It allows for you to build a string in .Net and then easily convert to DateTime using a format string.
For example:
Imports System.Globalization
Dim uk_culture As New CultureInfo("en-GB")
Dim dateString As String = "Sun 15 Jun 2008 8:30 AM -06:00"
Dim format As String = "ddd dd MMM yyyy h:mm tt zzz"
Dim result As DateTime
result = Date.ParseExact(dateString, format, uk_culture, DateTimeStyles.AdjustToUniversal)
September 7, 2010 at 10:40 am
Make sure you have mapped the right field to OLE DB Destination. If possible re-create the OLE DB Destination component and re-map.
Amol Naik
September 9, 2010 at 1:12 am
Hi,
You have to check the DataType of Both the columns. i.e,if you have to convert a column of datatype DT_WSTR to DT_STR then the first error (Columns "Derived Column T_TIME" AND "T_TIME" cannot convert between unicode and non-unicode string data types.
) appears. Either make them both as DT_WSTR or DT_STR
Regards,
Amar Sale
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply