Helpful information and examples on how to use SQL Server Integration Services.
What’s new in SSIS? In a word: Lots.
It’s been a lot of fun showing off the new SSIS features and functionality – the hardest part has been fitting it all into a 60 minute timeslot. This CTP is close to feature complete – the rest of the time we have for the Denali release will (mostly) be spent improving and polishing what’s there. However, you can expect a couple of new features to slip in for the final release - we have to leave some surprises for RTM, right?
There have been two major themes for SSIS in the Denali release – improving the Developer Experience, and making SSIS solutions easier to Deploy, Configure, and Manage.
We wanted to make the designer easier to use, as well as deliver productivity enhancements to delight our existing users. Some of the major features here include:
The deployment and configuration improvements revolve around the new SSIS Catalog (or SSIS Server). This catalog is essentially a SQL application - a user database on a SQL instance (SSISDB) with a set of stored procedures and a T-SQL API. In addition to the server, we’ve introduced a new Project model for bundling your packages together to simplify deployment, and a new Parameter model to make configuration and management a little more straight forward. This CTP contains a number of enhancements to what was delivered in the previous CTP, as well as a bunch of new features, including:
We have made further improvements to the new functionality that appeared in the previous Denali CTP – thank you all for your feedback!
In addition to the major themes, we’ve made many small enhancements and quality improvements through out the product. Some of the more noticeable ones include:
Here are some additional links if you’re looking for more information.
Does this CTP (3) break BIDS 2008 as the CTP1 did?
Looking forward to the series. Of course, I'm more looking forward to RTM to get some of this functionality into production!
ArthurZ, no - the CTP1 release notes stated that it couldn't be installed SxS with 2008. Since we are providing a brand new BIDS shell (VS 2010), it should install just fine with BIDS 2008. If you run into issues, report it on Connect.
Thank you for the quick reply Matt!
I just seen that VS2010 may actually break and that I need to re-apply SP1 to fix it.
Besides, the Data Quality Services link takes to a page that has no content, any other to learn more about it?
The Data Quality Services team will be posting more information soon. You can also check out the link at the end of the post for their TechEd 2011 presentation.
Are their any improvements to Web Services support in this version ? The fact that We can't connect to Salesforce.com (amongst others) without developing custom code is likely to mean we move to another ETL tool in my organisation
Leigh - there are some minor bug fixes for the Web Services task, but no major improvements. Due to the nature of web services, I typically recommend using a Script Task / Script Component instead. Using proxy classes from scripts was made a lot easier in 2008, and continues to work well in Denali. Scripts give you more control over the data, and allow you to format it as you'd like.
If you're looking specifically for Salesforce.com integration, CozyRoc supplies a custom connector for SSIS. www.cozyroc.com/.../salesforce-source
Will Crescent be available for Standard Edition of SQL Server or only Enterprise Edition?
You'd need to ask on the RS forums / blogs about Crescent, sorry!
I have installed SQL Server Denali CTP 3. It looks great. My favorite is SSIS. Look and feel is fabulous. features such as DQS and shared datasources, etc are really great.
SSIS Denali will be changing the ETL world once again.
Before I get started I just want to say I'm not shooting the messenger here, but I can't leave this alone....
"•Flat file source supports embedded qualifiers"
This is utterly embarrassing for Microsoft to have to add to the list of features or improvements. This is Database-101 and worked fine in SQL 2000 but for some reason was removed or bugged in 2005 and 2008 (yet the text qualifier setting was available in the Import Wizard...go figure). To boot, MS products (confirmed in my version of Excel 2000 and Excel 2010) export data with text qualifiers yet SQL 2005 and 2008 wouldn't import them (at least not without a workaround or two).
I'm absolutely confused as to why it wasn't fixed back in 2006 when MANY reported it. We'll see if it's actually back in place like SO many want it to be...of course I won't see this until we upgrade in another 6 or 7 years.
Is there a reason why connections in the global Connection Manager do not accept expressions? Because of this, I do not see any feature for portability, and hence no need for global connection managers. It would have great to have expressions for a global connection, instead of having to create local connections, and setting up the expressions individually.
thisfutile1 - supporting embedded qualifiers can actually have a pretty significant impact on performance of flat file parsing. If you look around, you might find that many parsers out there don't support them for that reason. we're pretty good about mitigating this performance impact in Denali, but it's something to keep in mind. That said, we pay close attention to feedback and issues reported through MS Connect. Like all development teams, we have finite resources, and can't address every issue in a release, but we do our best to make our users happy.
Hi Ian - this was a limitation in CTP3 that has since been addressed. You can now set expressions on project level connection managers (but any parameters the expression references must also be declared in the project scope). Can you explain why you need to set an expression on the project connection manager? are you trying to update the value at runtime from within the package?
I have some text mining/analytic's coming up and I was wondering if their are any improvements in that area ? The Term lookup / Term extraction don't really cut it in 2008 r2 (correct me if I'm wrong, but they were unchanged since 2005).
Is the current CTP feature complete ?
The current CTP is almost feature complete - we announced some new features at SQL PASS as well. I'll be posting some new content once it is publically available.
As for Term Lookup / Term Extraction, no - I believe some minor bug fixes were made, but there are no new features added to these transforms for SQL 2012. If you're looking for matching functionality, you might take a look at the features in Data Quality Services.