I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do): I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leavea NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like: isnull(foo)? newid() : foo But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()? Or do I have to craft a SQL statement in theLookupthatguarantees a non-NULL result?

what i think you need to do is send only the rows that have a null value to the error output of the lookup transformation. you can then link this error output to an ole db command transformation to update the rows that have null values (which should be every row) with guid values. then, you can merge the output of that ole db command transformation with the non-error rows from the lookup transformation.

Thanks for the tip, Duane. But I don't know how to follow through and implement it: The error output from the Lookup transformation has 2 new DT_I4 columns named ErrorCode and ErrorColumn, but the DT_GUID lookup column (named item_id) is not there. What is the SQL Command for the subsequent OLE DB Command transformation to (1)verify that the error was a lookup failure (i.e. NULL value for the item_id column) and (2) add the DT_GUID item_id column with a value of newid()? Thanks again!

Here's what I'm using now. The frustrating thing (there's always something with SSIS) is that this technique is working in one of my data flow tasks but not the other. Anyway, the idea is that the Lookup error outputis redirected to a subsequent Lookup whose output will be Union'ed back together with the output of the first (successful) Lookup. The key is that the second Lookup generates a single row with arbitrary values (e.g. NULL) in the join columns and a new GUID in the result column. The way this is done is by going to the Advanced tab on the Lookup transformation editor (not to be confused with the advanced editor for the component!) , checking Enable memory restriction, then checking Modify the SQL statement, and then entering something like this:select * from (select cast(NULL as int) as join_column_1, cast(NULL as varchar(20)) as join_column_2, newid() as result_column) as refTable My understanding is that the check boxes disable caching for the Lookup and the query is run once for every row. I generate the unused join columns because I reuse the same Reference Table (whether table or query) from the first (successful) Lookup. I use the "select * from (...) as refTable" syntax just because that's how the SSIS-generated statement waswritten. Is this a reasonable solution? Can anyone explain why this technique would work in one situation but not another? Thanks!

Kevin Rodgers wrote: I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do): Why not? If it works - why not use it? Kevin Rodgers wrote: I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leavea NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like: isnull(foo)? newid() : foo But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()? Or do I have to craft a SQL statement in theLookupthatguarantees a non-NULL result? There is no NEWID() functoin and you're right, there probably should be. But I'm puzzled as to your assertion that iSSIS expressions should mirror T-SQL. T-SQL is a set-based data manipulation language. SSIS expressions are for inline manipulaiton of atomic values. That isn't comparing apples with apples. Its wrong to assume that just because some functonality is in SQL Server database engine that it should be in SSIS too. SSIS expressions are deliberately different to T-SQL because they fulfill different purposes. -Jamie

I spoke too quickly and should not have impugned SSIS: The technique is working in both cases, it was just that I misinterpreted adownstreamerror as being caused by a failure of the second Lookup component (which I've named "Generate result_column").

Jamie Thomson wrote: Kevin Rodgers wrote: I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do): Why not? If it works - why not use it? Because I don't know Visual Blah and have no other need to learn it. But if someone would provide a recipe for this particular problem, I'd use it. Jamie Thomson wrote: Kevin Rodgers wrote: I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leavea NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like: isnull(foo)? newid() : foo But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()? Or do I have to craft a SQL statement in theLookupthatguarantees a non-NULL result? There is no NEWID() functoin and you're right, there probably should be. But I'm puzzled as to your assertion that iSSIS expressions should mirror T-SQL. T-SQL is a set-based data manipulation language. SSIS expressions are for inline manipulaiton of atomic values. That isn't comparing apples with apples. Its wrong to assume that just because some functonality is in SQL Server database engine that it should be in SSIS too. SSIS expressions are deliberately different to T-SQL because they fulfill different purposes. They both compute values from columns and variables. The only difference I can think of is that T-SQL of course provides aggregate functions, which SSIS segregates in the Aggregate data flow transformation -- a perfectly reasonable design. But I can't think of any reason why any scalar T-SQL function should not be available in an SSIS expression (or why any aggregate T-SQL function should not be available via the SSIS Aggregate component). Similarly with operators of course, although here SSIS might be more functional than T-SQL -- I love the ternary ?: conditional!

To address the question of why the SSIS expression language does not contain a NewID() type function: basically we ran out of time. I encourage you to head over to https://connect.microsoft.com/SQLServer/feedback and submit a suggestion for adding this functionality. Thank you for your feedback thus far -- it is always good to know what functionality people are craving.Mark

Kevin Rodgers wrote: Jamie Thomson wrote: Kevin Rodgers wrote: I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do): Why not? If it works - why not use it? Because I don't know Visual Blah and have no other need to learn it. But if someone would provide a recipe for this particular problem, I'd use it. No problem. Drag a script component oto your data-flow. When prompted for it to be a source, destination or transformation, select Transformation Drag a data-path (i.e. a green arrow) to your new component Open it up and go to the "Inputs and Outputs" tab Expand "Output 0". Select the "Output Columns" folder and click the "Add Columns" button. Add a new column called "GUID" and give it a data-type of DT_GUID Click on the "Script" tab and click the "Design Script..." button Delete all the code and copy in the following: Imports SystemImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperPublic Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.GUID = System.Guid.NewGuid End SubEnd Class Voila! There is your NEWID function! Kevin Rodgers wrote: Jamie Thomson wrote: Kevin Rodgers wrote: I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leavea NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like: isnull(foo)? newid() : foo But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()? Or do I have to craft a SQL statement in theLookupthatguarantees a non-NULL result? There is no NEWID() functoin and you're right, there probably should be. But I'm puzzled as to your assertion that iSSIS expressions should mirror T-SQL. T-SQL is a set-based data manipulation language. SSIS expressions are for inline manipulaiton of atomic values. That isn't comparing apples with apples. Its wrong to assume that just because some functonality is in SQL Server database engine that it should be in SSIS too. SSIS expressions are deliberately different to T-SQL because they fulfill different purposes. They both compute values from columns and variables. The only difference I can think of is that T-SQL of course provides aggregate functions, which SSIS segregates in the Aggregate data flow transformation -- a perfectly reasonable design. But I can't think of any reason why any scalar T-SQL function should not be available in an SSIS expression (or why any aggregate T-SQL function should not be available via the SSIS Aggregate component). Similarly with operators of course, although here SSIS might be more functional than T-SQL -- I love the ternary ?: conditional! Well right there is one reason not to copy T-SQL. For the environment that the SSIS expression language is used in its syntax is more suitable than T-SQL. Can you imagine writing a multi-clause CASE statement in an Expression? Not pleasant at all! Remember also that the SSIS expression language is data-platform agnostic. It is provided to work with data from files, web services, XML documents etc... as well as databases and hence it shouldn't be tied to a particular data-platform's procedural language. Just my two-penneth worth! -Jamie

Mark Durley wrote: To address the question of why the SSIS expression language does not contain a NewID() type function: basically we ran out of time. I encourage you to head over to https://connect.microsoft.com/SQLServer/feedback and submit a suggestion for adding this functionality. Thank you for your feedback thus far -- it is always good to know what functionality people are craving.Mark Mark, Turns out someone has already requested this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126063 -Jamie

Some examples of guids in SSIS: http://microsoft-ssis.blogspot.com/2011/02/create-guid-column-in-ssis.html The Script Component solution is quite simple (even for people who never used C# or VB.net). Only one row of code. How hard can it be? :-) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com