blogs.conchango.com

Here are just a few...

1. Scripting design tasks are displayed modally with respect to the rest of visual studio.  As SSIS is case sensitive, we could avoid those annoying typos by getting the ability to move to the variables and copying the exact spelling of the variable we need to pass, check on default values and otherwise move along efficiently when we just need to go back and check something while writing code.

2. Create a control flow item that replicates the merge function in SQL 2008 and Oracle 9i, but can be used on other RDMS systems as well.

3. Allow changes to SSIS logging programmatically (e.g. change a configuration value that will allow a switch between basic and verbose logging).

4. When you copy and paste tasks between packages or containers, you end up with a mess.  Is there any way to keep it neat?

5. Allow copying of variables without directly editing the XML.

Hope that helps.

Mike

PingBack from http://blog.avanadeadvisor.com/blogs/old_school_database_guy/archive/2008/04/10/10849.aspx

I would like to see expressions be available for use in file connection managers, specifically when pointing towards file system paths of other (.dtsx) files. I believe that would make better use of making the config file a more centralized point of runtime configuration. I understand that you can add that parameter in the DTExec command, but most are using config files. I would find this most useful when trying to move from Dev to Test to UAT to Production and using UNC paths in the connection manager.

Better Excel (2003 primarily) and CSV support.  I pull in data from those formats regularly and it's a beating in SSIS.  First you deal with the wonderful "Excel thinks this is a float so anything that's not a float is NULL" behavior.  I've tried all sorts of different hacks to make Excel treat things as text, to scan more rows, etc - nothing works and I end up dropping postal codes and other data because Excel insists that they are a certain type.

For my next trick I export that out to a text file - CSV or Tab delimited.  That works until it hits a row that doesn't contain all of the columns because Excel exports it that way!  (How do you emphasize that better?)

So I go back into Excel, add a dummy column fully populated all the way through the file.  Export to a text doc again.

Now I can pull it in using SSIS as long as I define the columns for the text file.  I also have to define each file where Excel had the ability to import multiple sheets as long as I didn't mind dropping data.  :)

So back to my original point - just make Excel work.  It would save quite a bit of time messing with all of the imports/exports that I have to go through now.

One more while I'm at it.

Delimited Text files - give us the ability to have EOL characters override the number of columns expected.  That way if a row doesn't contain all of the columns (because of e.g. an export from Microsoft Excel per my earlier comment), we can proceed without getting columns containing lots of data due to flowing over the EOL character.  Once again, I deal with this quite a bit in my day to day tasks and it's painful and time consuming.

Nice! I live in Seattle, so welcome! I really like SSIS in general but here are some suggestions:

Peeve 1: I'd like better support for local paths, for packages deployed in the file system. We made the decision to deploy our packages like little programs and schedule them with Windows Scheduler instead of inside SQL Server / SQL Server Agent. One unanticipated hassle has been, for example, that I would like to pop open Package Configurations and say the default config for my package is in "./mypkg.dtsConfig"; however that is apparently unsupported and definitely doesn't work in BIDS. Right now this is a WAD, but I see no reason why it couldn't be improved.

Peeve 2: I want to be able to specify a package config file in the Execute Package task, instead of using the default (am I missing something?).

Peeve 3: the whole Save Sensitive with User Key / Don't Save Sensitive setting vs. connection strings and passwords in config files is INCREDIBLY confusing for new users and could stand some clarity in the way it's designed and presented. Me, a little slow perhaps, I took a whole week just to figure out some combination that would work for my team. And it's definitely not the default.

Peeve 4: it would be very handy to be able to control the order of load for parent/child rows within one data flow, so you don't have to go through all sorts of gymnastics to prevent false foreign key violations (you blogged on this one a while back) when legit child rows are loaded before the parent rows.

Peeve 5: everything Peter said about Excel (see http://thedailywtf.com/Articles/Jan48.aspx featured comment containing my rant about Excel and actual Data). Actually, if the Excel team at MS could just pull their heads out of the sand about this issue and make Excel itself less hostile to the data people attempt to put in spreadsheets, that would be super, and the SSIS problem would be simpler to solve. People have been trying to use Excel for data for a long time, and it's been a bad fit for that purpose for a long time, yet the Excel team has done nothing to really address the problem. How about, for example, strongly typed cells? But I digress...

Peeve 6: I can't find a way to add an existing package file to an existing BIDS project without having VS duplicate the file (maybe I am just not doing it right). That's quite an annoyance.

Peeve 7: The XML in files created by the config file tool is not formatted; it's all on one line. My attempts to have VS autoformat the file ruined the files in every case, so I've resorted to a third party tool (Jedit + a plugin) to reformat the XML with line breaks and indentation, then I re-open them in VS to edit. People do have to edit these files after all, and one looong line of text is not too nice. It would also be helpful if the config files were automatically added to the project's "misc" folder upon creation.

Peeve 8: SQL Server is very, very often deployed without case sensitivity, but SSIS is .NET based and case sensitive by default. This causes a LOT of bugs as people don't necessary find all the places where that has an impact. (see the documented issue with Lookup for one example, there are others). It would be keen if this were smarter, e.g. let's have the program examine the data source and see which behavior is appropriate, or let's have a setting to make a package or a whole project case-insensitive (again, unless I missed that setting and it exists :-)

I would like to see more flexibililty in processing Analysis Services cubes.  Specifically, I have a package that is going to process cubes nightly; my user ID is does not have access to do so in production (and never will).  I want to run a package dynamically with a configuration file to set the Analysis Services connection string dynamically.

The Analysis processing Task does not take connection string (with User ID and Password) to connect for processing.  When I pass in the User ID and Password, it is essentially ignored, and run using the credentials of the account logged in to the machine.

Peeve 6 - The default or most obvious way is very annoying I agree. However try this - In Visual Studio, with your SSIS project open, follow the menus - File, Open, File, then select the package. The package will open, but it will not be part of the project. Now from the File menu goto the "Move <FileName> into >" option and select your project.

1. A better managed code object model to develop against

2. More code-behind like functionality for advanced users

3. Equal support for C#/VB.NET for all coding

Please let them know as no one listens to me :).

The ability to have configuration applied from SQL agent that was invoked prior to to any other configuration (i.e. so SQL agent could be used to run multiple versions of the same package which pointed to different SQL Configuration tables)

Performance tuning, having access to more detail on what is actually hapening in phases like validation and pre-execute would help in diagnosing perfomance issues

Task precedence to force the blocking of certain tasks before another has finished.  I have one scenario where I am pairing up transactional data to start and end events with a complex data flow doing various lookups and data manipulation so would not want to repaet that logic, and writing out to a raw file increases the overhead by having to manage files and reduces the portability as increased the number of things likely to error between environments.

I think the cache connection manager in 2008 is likely to solve many other performance issues, but have not had the chance to give it a bit of stress yet!

Phillip,

You may check CozyRoc's Data Flow Source/Destination components, which will help you avoid writing to raw files entirely. I believe you will also gain performance boost as well. Let me know if you need help if you decide to use them: info at cozyroc.com

PingBack from http://www.pythian.com/blogs/955/log-buffer-93-a-carnival-of-the-vanities-for-dbas

I realise I'm a few weeks too late, but top of my wishlist is:

Add an option (probably on the package object) to perform implicit datatype conversions - a la T-SQL or DTS.  These would kick in in place of the current validation errors etc when datatypes dont match exactly.

This would speed up SSIS development a lot - in particular maintenance overhead when source data changes.  Presumably there would some performance impact, but for most scenarios this doesnt matter.

Hi

I am just a beginner and of all the ETL tools out there SSIS documentation is much to be desired.

I would prefer a cookbook and training videos from microsoft.

regards

Hrishy