Derived column returning no data on GetDate() function

Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?

Hi Jonathan,

That's very odd indeed. Have you by any chance changed the type of the derived column, or is it still DT_DBTIMESTAMP?
Are you seeing any errors at all?

Thanks
Mark

|||

Hi Mark--No errors, and no change to the data type. Just a return of a NULL value on each row in the result set after stored in the local database.

I'm running through an online example now using the exact same GetDate() function with the AdventureWorks database now--not finished with the example yet on another machine installed with SQL Server 2005 SP1 Developer Edition, so I can't say whether this is reproducible yet.

- Jonathan

|||If you put a data viewer on the path between the derived column and whatever is after it, is the result already NULL at that point?|||

Hi Mark--I'm not sure when it returns NULL; I didn't have a data viewer on hand to do so. I'm new to Visual Studio and Integration Services, so I know that I need to isolate the contents of the derived column as a variable at the time the step is executed, but am not sure how to do that in this product. What data viewer utility or resource would you suggest?

Jonathan

|||

If you double click on any of the path arrows between components, it will bring up the "Data Flow Path Editor" dialog. In the left pane, you should see a selection called "Data Viewers". Select that, and then hit the add button, and you should see a dialog with a few data viewer choices. For this purpose, "Grid" is what you want.

Once you have added a Grid data view to the path that exits the derived column, you can execute the package again (within BIDS) and you should get a dialog that shows a number of rows with all the columns and data values at that point, and execution will pause there. If the column is already showing as NULL in this viewer, then we'll have isolated the problem to the derived column, most likely.

Let me know what you find out.

Thanks
Mark

sql