December 18, 2013 at 3:29 am
My destination is a table in SQL server. My query is against the source, which is our ERP server. This package pulls data from ERP source into SQL server.
The column was varchar previously in the destination. We had to change it to ntext to allow unstructured data to be entered into it.
But then since it was not used in any calculations, we want to ignore it.
December 18, 2013 at 8:03 am
I don't understand what "unstructured" data has to do with needing NTEXT as a data type. NTEXT vs. TEXT is merely a matter of being able to store characters that are in a national character set, such as double-width characters like those in some Asian languages. Using TEXT or NTEXT has no practical benefit over the use of VARCHAR(MAX), so perhaps you could change that column to VARCHAR(MAX). Also, do you have any information to tell you what the maximum likely length of the data that could appear in this field is? You could potentially avoid VARCHAR(MAX) and go with VARCHAR(nnnn) instead, where "nnnn" is some number <= 8000. FYI, according to MS, the TEXT and NTEXT data types are deprecated, per the folliowing link:
http://msdn.microsoft.com/en-us/library/ms187993.aspx
Apparently, they've been deprecated since at least SQL Server 2005.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 19, 2013 at 12:06 am
Well, the internal implicit conversion changed the column99's datatype to NText automatically in the source editor, so I had to change the destination datatype to NText to match them.
But I'd removed the column99 from the destination and remapped everything. It ran fine today, like before. But tomorrow is always a question going by the trend of the failure rate of the package. I'd previously done this and it worked a day and failed the other.
December 19, 2013 at 11:02 pm
It might be interesting to note if there is a behavior change if the source query were to be encapsulated by virtue of creating either a VIEW or a STORED PROCEDURE on the ERP server. Then the SQL Statement for your source is either a SELECT from your view or an EXEC of your stored proc. Sometimes this kind of thing can avoid all manner of grief.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 5, 2015 at 4:10 am
Hello guys ,
I am also having Same problem "missing an escape character for a quote.Unable to update Primary key"
I am using ado.net connector to pull data from SAP to sql using SSIS. Sometime its work but some time its give me above error.I have tried all possible solution available on internet but still not yet solved my issue.
Anyone have any kind of solution for this.
Regards,
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply