When I first starting working with SSIS, I had a bit of confusion as to what the difference was between these two objects. Well, this quick SSIS tidbit will explain.
A data source is a connection string that is created at the project level. Once a data source is created all of the packages in that project can use it when creating a package connection through the Connection Manager. Data sources are optional, you can still create the package connection without it. Data sources are helpful in that they provide a way to change the connection string information in one place and then that change will cascade down to all the packages in a project that reference that Data source. Unfortunately, data sources come with some drawbacks, listed here in this article by Matthew Roche: Reusing Connections with Data Sources and Configurations.
Package Connections are connection strings saved at the package level. They can reference a project Data Source or they can hold connection string information independent of the data sources for that project.
So, now that you know the difference between the two, how can you tell them apart? Easy, just take a look at this screen shot. The package connection based on the data source has the same icon as the data source shown in the Solution Explorer:
You just can’t beat free, right. I had to do a little SSIS research for a work project and noticed all the great videos on SSIS on the web. So, I decided to do a little round up and list them all here. Enjoy!
- SQLShare.com has a nice list of SSIS videos on specific tasks.
- SSIS Videos (and more!) at MidnightDBA. You’ll enjoy the humor in these videos just as much as knowledge you’ll gain. Do a search on the list to find the SSIS Videos.
- List of official Microsoft videos on SSIS.
- The BI VC for SQLPass has an archive of their past presentations. Again, you will have to search for the SSIS videos.
- Pragmatic Works holds monthly webinars – some on SSIS. Once again, search through the list to find what interests you.
- Jamie Thompson (Blog | Twitter) has created several short SSIS videos – SSIS Nuggets.
I’ll add more to the list as I find them.
Here is another difference between DTS & SSIS that will trip you up if you are not expecting it. The issue deals with loading flat files with varying numbers of columns. For example the 1st row has 5 columns, then the 2d row has 3 columns, and then the 3d row has 4 columns. DTS handled this by always looking for the row delimiter first, whereas SSIS expects to always have the same number of column delimiters in each row. Here are some great posts that can explain this better than I can:
http://blogs.conchango.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx
http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_–Flat-File-Connection-Manager-issues.aspx
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
For some 3 years I worked in a shop that primarily did ETL work with DTS. I became quite familiar with all of the little quirks and idiosyncrasies of DTS, and I learned exactly how to get stuff done in DTS. DTS is like an old friend to me! But now I’ve started to work with SSIS – SQL Server Integration Services, the replacement for DTS that’s included with SQL Server 2005. The change to SSIS has been a challenge for me, particularly the separation of control flow from data flow. SSIS is vastly different from DTS, so if you have a need to start working with SSIS, do yourself a favor and budget some time to get up to speed. Believe me, you will need it!
