Heads-up! In MySQL, WEEKDAY('2012-11-09') = 4 (0-6 starting on Monday), but in SQL Server, DATEPART(dw,'11/09/2012') = 6 (1-7 starting on Sunday). If you’re extracting data from MySQL to load into SQL Server, the correct translation is ((WEEKDAY(date)+1)%7)+1.
Converting to Project Connection Across Multiple Packages in SSIS 2012
I’m migrating a Business Intelligence project from SQL Server 2005 to SQL Server 2012. Microsoft has, overall, done a great job with their development and migration tools, and some of the new features of SQL 2012 are great and will save me a lot of time going forward. One neat new feature in SQL Server Integration Services (SSIS) is Project Connections: you can define a connection at the project level, and all packages in the project automatically inherit a reference to that connection.
So this project I’m migrating has maybe 40 packages, many of which had the same two connections (primary source application and the DW database). In SQL Server Data Tools, you can open a package, right-click on a connection, and “Convert to Project Connection.” So far, so good. Problem is, all those other packages that have a connection of the same name will not inherit the project connection because the local one overrides it (by design). And if you open another package and delete the local connection, every task and data flow component that used that connection gets the dreaded red “X” icon–they don’t automatically revert to the project-level connection with the same name. Best I can tell, the only way to fix it in SSDT is to reconfigure every one of those broken tasks and components. The Internet is full of articles showing how to convert a connection in one package, but nothing gave me any clue what to do with the other 39 packages. I couldn’t accept that I would have to do all that–there must be a better way. Continue reading “Converting to Project Connection Across Multiple Packages in SSIS 2012”
