Anyone who has used a software tool will tell you that its impossible to know that tool inside out after just a few dys of using it and of course SSIS is no different.
As you get to use SSIS then your knowledge improves and achieving your SSIS goals becomes alot easier. However, attaining that level of knowledge is not easy. That are alot of foibles in SSIS that you need to know, things that won't be documented, and that sort of knowledge can only be acquired by getting down and dirty with the product and actually using it.
As such, I had the idea that posting a list of tips, tricks, guidelines and similar for new users would be useful. The list is small to begin with but I expect it to grow over time and I'm looking for alot of input into this. In other words, I need your help!
If you have any suggestions then add them as a comment (N.B. Anonymous comments are disabled so you will have to register and login, otherwise email me) and if I think its a good suggestion I will add it to the list below. And remember that this is intended as an aid, so try and be polite! This isn't an invitation to whinge at me about things that don't work or that you think could/should work differently.
Without further ado, here is the burdgeoning list. I hope it proves to be useful.
- In Derived Column component it is not possible to replace a value in a column with a value of a different type
- Synchronous components output the same number of rows as in the input. Asynchronous components may not.
- Synchronous components output data in the same buffer as is input. Asynchronous components use a different buffer.
- If you get a warning on an OLE DB Source component when connecting to Oracle you can probably fix it by setting AlwaysUseDefaultCodePage='True'
- You can change the default type mappings that SSIS (and the Import Export wizard) uses for connecting to external sources by editing the XML files at C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles (credit for this one to Pete Schott)
- If you are dynamicly setting connection strings of connection managers at runtime then you may need to set DelayValidation=TRUE on the tasks that use those connection managers (Pete Schott)
- When storing connection manager properties in a configuration file you only need to store the ConnectionString property as this is an amalgamation of all the other properties.
- SSIS will not store passwords for you in a configuration file as this is considered a security risk. You need to manually edit the configuration file yourself to enter the password.
- Setting IsSorted=TRUE on a component output does not sort the data for you, it only tells the engine that the data is sorted. Only set this property to be TRUE if you know for sure that the data is sorted.
- When using OpenRowset Using FastLoad in the OLE DB Destination, the component will attempt to commit records in batches, the size of which is determined by the FastLoadMaxInsertCommitSize property. If you have set the component to divert failed rows then in the case of an insertion failure all rows in the batch will be diverted to the error output. Hence, you are likely to see perfectly valid rows in the error output..
-Jamie
UPDATE 2006-10-23: Philip Coupar, pointed out that you need to login in order to post a comment so I've updated this post accordingly.