You can use variables to modify connection options like server, table and column names in some SSIS objects. It really depends on what you are trying to achieve.
I read a bunch of that "I Hate SSIS" list you linked to. I disagree with some of the complaints. For example, he complains constantly (several entries) about how SSIS will ask you to confirm that changes you made in a connection string don't mean you want to redo your queries and such for that connection. This can all be avoided by changing one setting in the SSIS package properties. It takes about 1 second to change it, and this is mentioned in the documentation.
He's also wrong about the security needs. I don't know why he thinks SSIS packages need sysadmin rights to run inside jobs scheduled through SQL Agent. They don't. I can't tell what he's doing wrong from the complaint (he doesn't amplify on that point), but simply put, the statement is just plain wrong.
I don't know what UI he's using for SSIS. BIDS doesn't have half the problems he complains about, and BIDS is the default environment for building these. I can't even figure out some his complaints, because I can't reproduce them in BIDS. For example, the complaint about errors in script tasks being "displayed" in hidden pop-ups (pop-unders?). I've never had that happen, and I've been using SSIS since SQL 2005 was in beta. If the script contains a pop-up/modal dialog of some sort, which it generally shouldn't (unless the package is meant to only be run attended, which is uncommon to the point of being useless), it might do that. I've never tried using dialogs in SSIS packages and can't see why anyone would.
He also complains about column transformations. None of the complaints are valid in any version of BIDS I've ever used.
I've also never had his problems with source control. He probably has that misconfigured in some way. I've always used source control for SSIS, and never had any of the problems he complains about.
He complains that SSIS can only use VB.NET as "the only way to write actual code". SSIS can access and use any .NET DLL. Again, he needs to RTFM. Not a problem with the product.
He obviously didn't bother to look into the ability to handle data errors at all. There are examples in BOL (same on MSDN). It's extremely easy. His complaint about finding "the 1 row in 1,000,000" that has a problem usually takes me a few seconds, mainly depending on what I'm trying to do with the data and the power of the machine I'm working with.
I'm by no means a serious SSIS expert, and even I can tell that this guy really just likes to complain and doesn't want to read the documentation when he has a problem. Complaining is more fun, no question about that. It's nice to vent periodically. But it's doesn't solve the problem.
(Sorry if this goes on a bit. His list is quite long and eggregiously erroneous. SSIS is a nice tool, but it's just a tool, and definitely worthy of neither hate nor love.)
- 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