September 16, 2011 at 8:48 am
I'm not sure if this is the right place to post this, but since I don't see a dedicated forum under SQL Server 2008 for either SSIS or BIDS, I'm posting this under General.
While I love the improvement in the concept of SSIS over what was available with DTS, the actual implementation has been very unsatisfactory for us -- to the point that our director is researching third-party alternatives. The biggest issue we have is that both SSIS and BIDS have been unreliable.
With SSIS, we have a couple packages that work fine in the BI debugger (when BI itself doesn't stop responding), but tend to freeze at different stages when running as a task under scheduled tasks in the agent in production. The packages usually seem to get stuck in one of a few different Execute Process tasks that ZIP files (we've been using 7zip but are now testing WinZip to see if it works better for us) or, more rarely, SFTP using WinSCP. The packages do not fail; they just get stuck somewhere during execution and never complete to report either success or failure. (I am adding timeouts to all of the Execute Process tasks on the version I am developing now to see if that will at least allow them to fail gracefully.) What is surprising is that there does not appear to be an option at the package level OR within the SQL Server Agent to set an overall timeout after which the package fails.
BIDS, on the other hand, frequently gets stuck and stops responding when I attempt to save and/or run a package in the debugger. When this happens, the only solution I have found is to reboot the machine, as I am unable to even kill the process using the Task Manager. I have read some articles that suggest working in offline mode and configuring certain tasks to delay validation. While these may keep the product from freezing (I haven't tried them long enough to be sure yet), they remove enough functionality that it isn't long during development before I attempt something that tells me it can't work while the restrictions are in place and requires me to go back online before I continue.
The other headache we have involves updates. While we like the ability to use package configurations to store values that change between development and production, it seems the only way to change these even one of these values is to run the installation wizard. I suspect that it would technically be possible to skip this by simply editing the XML configuration file that is written to the server when a project is deployed, but I don't have access to the systems to actually test this.
Has anyone else noticed similar issues with these products? Do you have any ideas on how we can resolve some of these issues, or are there any better ETL alternatives available for either SSIS/BIDS or the SQL Server Agent?
--Andrew
September 16, 2011 at 11:11 am
I used to have problems with BIDS freezing up and requiring a reboot, but haven't run into that in several years. Is it possible you have a missing patch/service pack? I think I remember one of those solving the problem for me.
On file operations trying to take infinite time to finish, I have run into that with both 7Zip and GnuPG (encryption). I had to change the sequence of file operations to get that fixed. The package was creating a folder with the usual YYMMDD format, exporting files to that folder, then encrypting or compressing them in there. Asymetric file access at the O/S level would hang that up indefinitely sometimes (about once or twice a month). So, instead, I have it export to the same folder every day, do all the file stuff in there, and then move them to a date-specific folder as the final step. Since the folder already existed, no asymetric file access, and no more SSIS hang-up.
The places I've had to delay validation are places where I need it delayed for the package to run at all, either manually or in a scheduled job. Like variable-driven file names in connection strings. Haven't had a problem in development or deployment because of that.
I use put configuration data in a database. Yeah, it can be done in a file, but I'm a DBA and I like databases, so I use them for that. Makes it easier to work with the config data, too. Don't need file-level access, just access to the database. I use a database called DBA for that kind of thing, but you could legitimately do this in msdb, I guess.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 23, 2011 at 10:08 am
GSquared (9/16/2011)
I used to have problems with BIDS freezing up and requiring a reboot, but haven't run into that in several years. Is it possible you have a missing patch/service pack? I think I remember one of those solving the problem for me.
I have automatic updates running on all Microsoft products, so as far as I know it should be up to date. Is there an easy way to tell for certain? The About... info on SSMS doesn't say anything about service packs, but the version number for SSMS and the SSIS designer tools in BI all say they are version 10.0.4064.0. The Windows Update history shows that Microsoft SQL Server 2008 Service Pack 2 (KB2285068) was successfully installed on 2/?3/?2011 at 9:43 AM (after a previously failed attempt at 9:20 AM the same day).
These repeated reboots are wearing my patience a little thin. :angry:
GSquared (9/16/2011)
On file operations trying to take infinite time to finish, I have run into that with both 7Zip and GnuPG (encryption). I had to change the sequence of file operations to get that fixed. The package was creating a folder with the usual YYMMDD format, exporting files to that folder, then encrypting or compressing them in there. Asymetric file access at the O/S level would hang that up indefinitely sometimes (about once or twice a month). So, instead, I have it export to the same folder every day, do all the file stuff in there, and then move them to a date-specific folder as the final step. Since the folder already existed, no asymetric file access, and no more SSIS hang-up.
Well, this is my next step. I'm going to try copying the jpg images that I need to ZIP to a local drive and zipping them there, instead of zipping them across the network. The rest of the items I need to ZIP are generated by the package itself, so I can easily write them to the same local folder and do the compression there as well.
GSquared (9/16/2011)
The places I've had to delay validation are places where I need it delayed for the package to run at all, either manually or in a scheduled job. Like variable-driven file names in connection strings. Haven't had a problem in development or deployment because of that.
I tried to turn delayed validation on for only the files whose paths are the result of expressions, but I can't tell that it is making any difference.
GSquared (9/16/2011)
I use put configuration data in a database. Yeah, it can be done in a file, but I'm a DBA and I like databases, so I use them for that. Makes it easier to work with the config data, too. Don't need file-level access, just access to the database. I use a database called DBA for that kind of thing, but you could legitimately do this in msdb, I guess.
I might have to try that. Is it easy to switch the table connection when deploying packages from development to system test to production environments? I have been using XML precisely because the connection information for the databases the packages connect to is one of the configuration values.
--Andrew
October 18, 2011 at 11:00 am
Andrew in WV (9/16/2011)
BIDS, on the other hand, frequently gets stuck and stops responding when I attempt to save and/or run a package in the debugger. When this happens, the only solution I have found is to reboot the machine, as I am unable to even kill the process using the Task Manager.
UPDATE: We recently upgraded Symantec Endpoint Protection from version 11.x to version 12.1 and thankfully BIDS has been more stable. I haven't had to reboot at all the last couple times I used it. :w00t:
--Andrew
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply