Another Reason to Avoid Data Sources in SSIS

I’ve often railed on the evils of Data Sources – the .ds files that you can add to your SSIS projects in Visual Studio. I tell people to avoid them whenever I’m talking about SSIS deployment, and I even gave them a mention in my SSIS Best Practices blog post last year:

"Don’t use Data Sources: No, I don’t mean data source components. I mean the .ds files that you can add to your SSIS projects in Visual Studio in the "Data Sources" node that is there in every SSIS project you create. Remember that Data Sources are not a feature of SSIS – they are a feature of Visual Studio, and this is a significant difference. Instead, use package configurations to store the connection string for the connection managers in your packages. This will be the best road forward for a smooth deployment story, whereas using Data Sources is a dead-end road. To nowhere."

And now there is even one more reason (as if there weren’t enough already) to avoid them: they’re going to complicate your package upgrade path from SQL Server 2005 to SQL Server 2008. This is due in part to the fact that the SQL Native Client provider name has changed from SQLNCLI to SQLNCLI10 in SQL Server 2008. The SSIS package upgrade wizard will take care of this in your packages and update your connection managers appropriately. But if you’re using Data Sources in your project, they will not be changed, so when you next open your properly upgraded packages the new (and correct) connection strings will be overwritten with the old (and incorrect) values and the packages will fail.

And that’s not what we want, is it?

So save yourself some pain and go with Package Configurations[1] instead, ok?

 

[1] In the context of full disclosure, I honestly don’t know if the upgrade wizard is intelligent enough to find and fix connection strings in package configurations either, but they’re still the better choice. 😉

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in Best Practices, BI, Deployment, Katmai, SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s