SQL Server 2008 - DTS Designer crash when editing query on Transform Data task
We are currently running some DTS packages to import data from Excel spreadsheets into SQL Server 2008 R2. The data is then processed and exported to flat text files for transmission to a third party partner. This was originally posted under SQL Tools, but was told it would be better serverd here. If you are going to reply, please don't tell me that I need to install the DTS and backward compatibility components or the PATH environment needs to be modifed, or that certain files need to be copied from one location to another, as I've already done that. Thank you. We recently set up the ability to run and edit DTS packages on two workstations for a couple of employees who will be taking over this process. This has worked fine on my workstations for the past 2 1/2 years. We followed the procedures laid out in Books OnLine to allow for view and editing DTS packages under Management Studio (installing the Backwards Compatibility components, as well as the DTS components for 2005). The new setups can open, view and run the DTS packages from their workstations, but whenever they attempt to edit the query pulling data from Excel, it crashes DTS Designer (and by extension, Management Studio). We've checked the Event Viewer and have found nothing there. I seem to remember this issue happening when we first migrated to 2008 and found an answer on the web, but cannot find any mention of it now. This is proving a bit frustrating, because the new guys will have to manually edit the SELECT queries to pull data from Excel, rather than being able to use the DTS Query Designer (the contents of the Excel spreadsheets change regularly). Any help would be appreciated. Butch Weber
May 17th, 2012 10:50am

Butch, Is is possible some or all machines received the new Office (or Excel) and / or the updated MDAC? If not, the next thing is to get to the bottom of what crashes exactly, perhaps you see a DOS window popping up, no? That would be the SQLDumper.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 10:58am

Butch, Is is possible some or all machines received the new Office (or Excel) and / or the updated MDAC? If not, the next thing is to get to the bottom of what crashes exactly, perhaps you see a DOS window popping up, no? That would be the SQLDumper. Arthur My Blog Arthur, Both machines are newly built laptops with Office 2007 or 2010 running on them. Is there a version of MDAC that has to be on there in order for this to work? I know I have it installed on my laptop, so I could ask them to try installing that if they don't have it. Also, I've sat with both of them when trying to edit the SELECT in Query Diesigner and no command shell windows pop up. It just dies, killing SSMS with it and dropping them back to the desktop. Nothing else is affected. Butch Weber
May 17th, 2012 11:05am

Is there some reason that you don't bring the packages forward to SSIS? The dts backward compatability was intended to be a stop gap and they have discontinued it in SQL 2012.Chuck
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 11:07am

I suspect the fact you are on such a new build of MS Office constitutes the issue. Thing is DTS was not produced at the time MS Office 2007 was shipped. Oh now SSMS is in play, interesting, definitely (IMHO) the issue is somewhere in the data provider. Let's do this: compare what is different, e.g. the MDAC version but I think I also need a screenshot of what / where they modify. Overall design is something I do not like, can you feed the query from a variable or file instead to the DTS package? I gave you errands but I trust we need that all Butch.Arthur My Blog
May 17th, 2012 11:11am

Thie version on the new laptops is MDAC2.8 SP1 on Windows XP SP3. This is the same version that I have installed on my computer, which works without issue. I've run Component Checker 2.0 on both machines; would the output of that report be what you're looking for? Butch Weber
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 11:42am

Not too useful at this stage. can you post an image of what they do that causes the crash?Arthur My Blog
May 17th, 2012 11:48am

Here you go. It's the Transform Data task between the Excel and SQL Server connections. All they do is click on the Build Query button below the SELECT syntax and it's crash city. FYI, it brings down SSMS becuase this is modal. The reason they have to edit the SELECT is because we get many different Excel files from a variety of users and the data being updated will change from file to file. Someone asked why don't we just go to SSIS? The reason is there are 27 of these packages. Each one generates a diffefent type of ile. Each one uses ActiveX sripts to manipulate the data, execute some stored procedures and write the data to a flat file. The DTS migration tool is pretty useless when ActiveX scripts are involved, so that would mean recreating all 27 packages. It has not been an issue up until now and I'm pretty certain the answer is relatively simple (but not obvious). Butch Weber
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 12:05pm

Yet the ActiveX scripts are not part of the SSIS 2012, now back to your issue, well can the end users not use the Build button, but the regular textual edit? Will that work? I can tell you how I would be looking for what is at fault: I would use one of the SysInternal's tools e.g. the Process Monitor to see what the Build button does to SSMS. Then you would be in a better position to formulate a remedy.Arthur My Blog
May 17th, 2012 12:29pm

Yet the ActiveX scripts are not part of the SSIS 2012, now back to your issue, well can the end users not use the Build button, but the regular textual edit? Will that work? I can tell you how I would be looking for what is at fault: I would use one of the SysInternal's tools e.g. the Process Monitor to see what the Build button does to SSMS. Then you would be in a better position to formulate a remedy.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 12:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics