SSIS lntext fields

YRKS
YRKS used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
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

Author

Commented:
I did this with dataflow and ole db command

Author

Commented:
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
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Reza RadConsultant, Trainer

Commented:
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

Author

Commented:
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
Consultant, Trainer
Commented:
I think problem is in Remedy driver too,
what will happen if you put a DATA VIEWER between your SOURCE and OLEDB COMMAND,
for this you should:
right click on green arrow between source and oledb command, and select data viewers, then add a data viewer as grid, let all settings as default.
this will create a data viewer which can help you to see what  is data right before oledb command,
if you see the ntext field's value as NULL there, so this problem is related to Remedy driver,
if you see the ntext field's value as values there should be, so problem is in your OLEDB command and probably in sql statement.

try it and let me know result

Author

Commented:
The data viewer shows null.
Reza RadConsultant, Trainer

Commented:
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


Author

Commented:
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 RadConsultant, Trainer

Commented:
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?

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
Thanks for all your support. I really appreciate it.
Reza RadConsultant, Trainer

Commented:
your welcome,
Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial