We're on the same exact version of ARS and also on SQL Server 2005. Based on your testing, there are a couple of options:
1. Use OLE DB as the data source for now to get around the problem (actually the preferred connection source according to the class I just took). Downside is it will require you to translate menus, etc. 2. Use ARODBC to pull everything but those large fields. Once that data has been loaded in SSIS, connect again using OLE DB to add those extra large text fields to the data before writing it out. This option gives you the benefit of the ARODBC translations while still being able to work around your problem and get the extra data fields prior to writing the data out. Craig Carter Software Engineer, RSP -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, March 18, 2008 11:36 AM To: arslist@ARSLIST.ORG Subject: Re: SSIS and Large Fields, importing large fields issues Yes, this refers to SQL Server 2005 Integration Services. We're using ARS 7.01 patch 5. We created several SSIS packages to populate a data warehouse with several Remedy tables (HPD:Helpdesk, CHG:Task, SHR:Audit, etc.) on a nightly basis. These are very simple jobs with no transformations involved. We're using DataReader Source and connecting through the Remedy ODBC driver to get the data, then saving it to a SQL table on our reporting server. For the most part these have worked fine, but we've run into a problem with text fields such as Description, Worklog and Audit Message. When there is too much texts in any of these fields (several paragraphs, not sure of the exact size), the SSIS job simply hangs when selecting the data. The step will turn yellow and the status bar reads "[DTS.Pipeline] Information: Execute phase is beginning." But it stays that way until I stop the job. We can select these fields if they contain a small amount of text. But the job chokes when we add a few K of text to those same fields. The text fields are being transferred as DT_TEXT or DT_NTEXT. In terms of the troubleshooting we've done: - The same SELECT statement works fine on the same machine when using MS Access or Crystal Reports, with both apps using the Remedy ODBC driver and same user credentials as the SSIS package. - We've verified that the "DataReader Source" connection works fine when selecting large text fields from other databases. - We've turned off validation (though the package validates fine) and increased the buffer size in SSIS, with no luck. Logging is no help either. - We've verified we can SELECT the same text fields by querying the T tables directly and not using Remedy's ODBC driver. - We're created the package from scratch on another machine with no luck. At this rate we're sort of dead in the water. Any insights or recommendations would be much appreciated... On Mar 17, 2:32 pm, Gidd <[EMAIL PROTECTED]> wrote: > Mr/Mrs Bird: > > One can only assume you are speaking of Microsoft SQL Server Integration > Services? > Can you help us to help you? > > What version of MS SQL are you using? > What version of ARS are you using? > > Regards...Gidd > > PS: Claire ... > > > > -----Original Message----- > From: Action Request System discussion list(ARSList) > > [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] > Sent: Monday, March 17, 2008 11:24 AM > To: [EMAIL PROTECTED] > Subject: SSIS and Large Fields, importing large fields issues > > We're seeing issues when moving (copying) data to our data warehouse when it > comes upon a work log field where there is a LOT of data it hangs, the job > never completes. We can cancel it and it doesn't error, it just waits. > > Anyone seeing this issue with SSIS and custom (Remedy) ODBC drivers. > I found this thread that validates that this isn't just our problem. > Unfortunately, there is no resolution in the thread or elsewhere that I > could find. > > http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1497581&Site... > > Do you have a support contract and contact for Remedy that we could open a > ticket with regarding this issue? I would believe that they may have seen > this with other customers and may even have an updated driver that works > around SSIS's limitations. > > I'll make an inquiry with Microsoft with regards to this issue and see if > there is a workaround within SSIS or a hotfix in the works. > > If those two paths are dead-ends then there are alternatives outside of SSIS > that we could explore using. > > ____________________________________________________________________________ > ___ > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.orgPlatinum > Sponsor:www.rmsportal.comARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > Platinum Sponsor:www.rmsportal.comARSlist: "Where the Answers Are"- Hide > quoted text - > > - Show quoted text - _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
