Start in the package Control Flow, with no object selected (Press ESC in the main window). Right click the background in the package, and select Variables Add a variable called LoopIteration with the Int32 data type in the package. Add a For Loop Container to the package Edit the For Loop Container by double-clicking it, […]
You are browsing archives for
Category: Integration Services 2005 / SSIS (DTS)
Converting SSIS Data Types to .Net Data Types
If you use Scripting tasks, the CLR or build components for SSIS, you will need to convert values from their SSIS types into .Net types. Here’s a quick list of the SSIS data types and their .Net companion. Integration Services Data Types Managed Data Type DT_WSTR DT_TEXT DT_STR System.String DT_BYTES DT_IMAGE System.Byte[] […]
Generating a new SSIS Package GUID with the dtutil Utitlity
If you make a copy of a .dtsx file, the Package ID property remains the same. This value is logged as the SourceID, and to ensure it is unique, you should generate a new value for any copies of a package. dtutil /FILE "Copy of sample.dtsx" /IDRegenerate Or the short hand version dtutil /FILE "Copy […]
Simon Sabin’s SSIS Raw File Reader
Ever used a Raw File destination in SSIS ? They’re FAST! Writing rows from the pipeline to disk as quickly as possible. Raw Files are useful for increasing the throughput speed of a data flow task by using them in Extract / Transform procedures. The reason they are so efficient is they stored the data […]
Using Expressions with SSIS Maintenance Tasks and SelectedDatabases
Its been noted by a few people that Expressions cannot be used to configure the Shrink Database Task at runtime. The problem is that the SelectedDatabases property is a collection which cannot be created by an expression. The same problem occurs in most of the Maintenance Task items of SSIS. Maintenance Plan Task components are […]
No!!! Undo, Undo, Undo!!
Or rather, why is there no “undo” in when working with Integration Services packages? This has been something that has me puzzled me since working with Beta 2 of Integration Services. I guess I figured it would be fixed by RTM, and just worked around the issue by regularly checking in finalised changes, and saving […]
SSIS Lookup with value range
Joe Salvatore called out for help on an interesting use of the SSIS Lookup component: Can you lookup a row using a value within a range? Joe specified his criteria as: DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey Easy! To show how it is done I’ve created a test database “SCRATCH” […]
How to use OUTPUT parameters with SSIS Execute SQL Task
Yesterday while trying to get OUTPUT parameters to work with SSIS Execute SQL Task I encountered a lot of problems, which I’m sure other people have experienced. BOL Help is very light on this subject, so consider this the lost page in help. The problem comes about because different providers expect parameters to be declared […]
Response to SSIS: Be wary of using SourceSafe
Jamie’s post touches on one potential problem of having SSIS packages that are too big: errors with Source Safe while checking in. SSIS Packages are saved as xml documents, SourceSafe is going to put in a lot of effort to save the delta changes of the xml between saves. While I haven’t encountered this problem […]
SSIS: Handling Lookup Misses
Let me direct you to an article by Ash on speed differences between two approaches to handing failed lookups in SQL Server Integration Services. Failed lookups are likely to happen at some point, and in most cases you won’t want your package to fail as a result, but rather a default or derived value or […]