asked on

SSIS lntext fields

I have a field1 in my table1 in Remedy which I need to pull in a SQL table2 field2.

Both field 1 and field 2 are ntext fields.

When I try getting the field1 value in excel sheet, the fields1 gets pulled correctly.

When I try doing the same thing in SSIS and update the field2 the value does not get updated. field 2 is null.

The field1 has special characters like ", ) , and new line
How would I handle these. do the special characters cause this?

Is there any other way I could update field 2 .

Any help would be appreciated.

Thanks

YRKS

Reza Rad

explain more about your ssis package....
how did you do this update?
with an Execute SQL Task?
or with data flow and ole db command?

and explain details of task settings ....
maybe you did something wrong

YRKS

ASKER

I did this with dataflow and ole db command

YRKS

ASKER

The other thing to note is all the other fields in the table get imported properly. iT IS ONLY THE NTEXT FIELDS WHICH HAVE LOTS OF TEXT ENETERED INTO THEM ARE DIFFICULT TO PULL.

Thanks

YRKS

Reza Rad

I tried a sample ssis package and that works correctly with ntext field via ole db command,
what is your Data Source Type? oledb?ado.net ?...?

and could you upload your package for trying to resolve here

YRKS

ASKER

I can do tjhis correctly from
SQL to SQl,
SQL to Excel
SQL to Crystal
Remedy (using Remedy d)river ) to Excel

Remedy (using remedy driver and SSIS) to SQL CANNOT DO THIS does not work.

I am assuming it is something to with the remedy driver.

I was wondering IF I can get this field in excel, I can automate this process in excel save it to a .csv and then upload it as job to sql.

Have never done programming with excel.
YRKS

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YRKS

ASKER

The data viewer shows null.

Reza Rad

so, problem is in SOURCE and seems remedy driver problem
you said you can import from source to excel? amazing because source shows null.
but if you can do it. use another data flow after this task
and read from excel and import into sql server

YRKS

ASKER

That is exactly what I was trying to do. and need help with it.

Also I will need to send the variable date to the excel . I have never done programming in excel so am hunting for it.

In excel I have a query which gives me the results with modified_date= gretaer thatn or equlal to 1-1-2010'

In actual this date has to be today's date - 5 days

Where modified_date >= todays date- 5days.

Was looking at how to do this.

And the call this excel sheet in SSIS

This date changes daily the date

Reza Rad

you should create a variable in package scope , and name it todayDate for example.
then write an expression to get today date, you can use GetDate() expression function.
then you should add an excel source, write your query there, like this:
select * from sheet1$ where modified_date>= ?
where the question mark ( ? ) is parameter
then you should set parameter with todayDate variable

that's all
is it clear enough?

YRKS

ASKER

I tried this.

The column which is giving me error is a ntext column. I can down load it to excel correctly. While trying to upload to sql server from excel it say there column is truncated and the task fails.

YRKS.

I must be missing something here.

Information: 0x4004300C at Extract data from uploaded csv file, DTS.Pipeline: Execute phase is beginning.
Error: 0xC020901C at Extract data from uploaded csv file, Excel Source [3726]: There was an error with output column "FullAnswer" (3759) on output "Excel Source Output" (3734). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.

YRKS

ASKER

GOt it. The thing is excel reads the first few rows and sets the datatype of the column.

Had to add in derived columns to get it working.

YRKS

ASKER

Thanks for all your support. I really appreciate it.

Reza Rad

your welcome,
Regards,