What's up with SSIS?

[ESquared]

I tried my first SSIS package today, and it was a very unpleasant experience. I can understand that it takes time to learn something new, but this seems like a joke.

Up until the point where I created a package and ran it on my own local computer, things weren't too bad. But trying to deploy it to a production server to create a daily schedule was the last straw.

Errors. Can't find the actions I need on any menu anywhere. The destination server doesn't have SSIS installed so does that mean it can't have SSIS packages installed in msdb? Copy the package to the server and double-click on it. No, this only gives the option to run the package, not install it to the SQL Server. Did we mention earlier that your connection logins would lose their passwords? And so on.

Of course, infuriating me along the way is the multiple second wait every time I tried anything new, while .Net compiled its program code. Sheesh! I would allocate 20 Gb of local storage on my computer to cache compiled .Net program code if that was an option and would get rid of the pathetic slowness of every .Net application and .Net web site. I can tell whether an app or site was created .Net just because it runs slowly. And this is "the next best miracle technology?"

I finally gave up, thinking I would just go back to lowly lovely old DTS and get the job done in 3 minutes. But of course, DTS won't accept SQL Server 2005 as a destination for some tasks. And no, we don't have a dedicated SSIS repository server here yet.

I've talked to a few people and they all tell me they've had a really rough time of it with SSIS. Is it just the extended learning curve and it really all is designed super well and I'll eventually love it and think it's fast and wonderful? Or does it actually have some serious issues that everyone struggles with?

 

[SQLDenis]

it takes a good 2 weeks to get to used to all the changes/idiocracies
once you get there you will be glad but getting there is hard
the most annoying thing is working with different datatypes not as big as a problem in DTS

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

The learning curve is probably killing you. It was brutal when I was first working with it.

If you don't have SSIS installed you can not deploy to the SSIS Repository. You should be able to change the setting to SQL Server which should let you save it in the msdb database. However if you don't have SSIS installed on the server you won't be able to schedule the package to run as it won't know what to do with it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

But trying to deploy it to a production server to create a daily schedule was the last straw.

To deploy your sis packages you can do it one of two ways depending on where you are storing your packages. in SSIS you are not required to store them in MSDB and I typically don't as I find it easier to do a number of things which I will mention later by doing a file system deployment.

1) Right click on your solution and select properties. Select the deployment utility set the "CreateDeploymentUtility" to true and then do a build on your project. this should place a compiled version of your packages along with a manifest into the \bin directory. Copy all of these files to your destination server and then run the .manifest file. This will walk you through a few screens that will deploy your solution. You will be giving the option for MSDB or FileSystem , again I prefer filesystem.

2) File System only. Copy your packages to your ~\Microsoft SQL Server\90\DTS\Packages. You can create a directory and place your packages in there.

I like the file system as you can update packages by simply copying and replacing your file. It is easier to backup as you only have to back up this directory and it's files rather than msdb.

Copy the package to the server and double-click on it. No, this only gives the option to run the package, not install it to the SQL Server.

This is one method to execute a SSIS package if you want to edit it you need bids and to properly edit it you need it to be in a Solution and not just open the file.

Did we mention earlier that your connection logins would lose their passwords?

Actually yes they do mention this in the documentation based upon the protection level you place on the package. There a a number of levels all of which do different things, but SQL BOL will cover the functions for the various levels.

Another way to handle data source and destination connections is to enable package configurations and store the configuration in one of a number allowed sources. Depending on how you do this there are different approaches.


My experience is that different people have different opinions based upon what they are trying to achieve. For peoples whose have written mainly procedures that move or manipulate small amounts of data seem to have more difficulty. Where people who do mainly large ETL and pump volumes of data in a single process seem to like it more. Another factor is how much besides SQL the individual knows. The Script tasks are probably one of the most powerful tasks around I have used them quite a bit. One Script task I wrote uses a SSAS olap cube as a data source and allows for direct MDX to be used rather than some of the other less effective methods.

It has a huge learning curve and does challenge people to forget the DTS way.

The after learning how to build packages the challange is then how to build optomized packages I can build 2 packages that do the eaxact same thing but by knowing wich transforms to use where and what order to put themin can mean the difference in 15 minutes vs 15 seconds.

If you need more help on something. I would be more than happy to help on or offline.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ESquared]

Thank you all for your comments. I will work through them in detail tomorrow, or as soon as I can.