February 6, 2008 at 2:01 am
Hi,
I have an Execute SQL task(calls a stored proc) which get's a list of items from a linked server.
I then have a For Each Loop and inside that another Execute SQL task which populates a datawarehouse.
The problem I'm experiencing is, some of the dateTime field values from the Linked server is null. How do I check if it's null & replace with some other date.
I cannot add the isnull(datefield,'01/01/1900') to the stored proc cos I get the error:
"No authorized routine named "ISNULL" of type "FUNCTION" having compatible arguments was found."
I'm assuming this is cos I'm selecting from an OPENQUERY.
February 6, 2008 at 4:03 am
Did you consider trying the COALESCE function?
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.
declare @testdate datetime
set @testdate = NULL
select coalesce(@testdate,'01/01/1990')
(ISNULL requires the same datatype for the arguments where COALESCE will do implicit conversions)
Toni (added explanation for ISNULL and sample coalesce code)
February 6, 2008 at 4:17 am
Hi Toni,
Thank you for your response, I don't think I was very clear as to where my problem is. Where about in my SSIS package do I make this check? How do I create the expression for this? My resultset is saved in a variable User::cmRowset. The dateField is mapped to User::cmRowset in my FOREACH loop
February 6, 2008 at 4:28 am
Sorry I could not be of more help. I had assumed you were asking about a replacement for the ISNULL function.
Another member should be able to answer this for you.
Toni
February 6, 2008 at 7:37 am
Hi,
Use a dataflow OLE DB Source to execute the stored procedure. Add to the pipeline a derived column component and add a new column which will replace the problem datetime column that you have. The expression for this column will be something like IsNull(OLD_DATETIME)?(DB_DATETIME)"01-01-1900":OLD_DATETIME. (Can't remember the exact syntax off hand)
Send these rows into a recordset destination, omitting your old column and replace it with the new derived column. Use the cmRowset variable that you used before and you should now not have any NULLs.
Then move on to your foreach loop as before.
Hope this helps
Kindest Regards,
Frank Bazan
February 6, 2008 at 7:59 am
Hi Frank,
These are the steps i took:
1) Control Flow I created an 'Execute SQL Task' which runs the sp & gets a list of items
2) Data Flow Task - then in Data Flow tab, I have an ole db which runs the same sp
2) Still in my Data Flow tab I have a derived column with expression:
Derived Column Name = dtEnded1
ISNULL(@[User::dtEnded]) ? @[User::dtStarted] : @[User::dtEnded]
3) Back to my Control tab I dragged the data flow task to my FOR EACH Loop. I do not see dtEnded1 as one of the variables to map to.....how do I set this part.....this is where my package is failing?
February 6, 2008 at 8:30 am
Hi,
1) Control Flow I created an 'Execute SQL Task' which runs the sp & gets a list of items
I'm not sure if this step is necessary as from the sounds of it, you are retrieving the same data twice.
2) Data Flow Task - then in Data Flow tab, I have an ole db which runs the same sp. Still in my Data Flow tab I have a derived column with expression:
Derived Column Name = dtEnded1
ISNULL(@[User::dtEnded]) ? @[User::dtStarted] : @[User::dtEnded]
Before you go back to your control flow, make sure that you have added a recordset destination component. Here you have to specify that you will be populating your object variable cmRowset (1st Tab). Also you need to specify the input columns omitting the old datetime with the nulls and replacing with your dtEnded1 (2nd tab in the component editor) Remember that all you're doing here is changing the task that populates the recordset. Previously you were using step 1 (Execute SQL task). I'm suggesting that instead you use step 2, and modify the datetime prior to populating the recordset. Ensure that cmRowset has the correct scope. i.e. make sure that it can be used by the foreach loop.
3) Back to my Control tab I dragged the data flow task to my FOR EACH Loop. I do not see dtEnded1 as one of the variables to map to.....how do I set this part.....this is where my package is failing?
You need to map the modified date dtEnded1 that you now have in your recordset, to an SSIS variable. On the Variable Mappings tab of the foreach loop editor, click an empty row at the bottom. Select the variable you want your new date mapped to and assign it the index value that corresponds to the position of the column in the recordset.
I think at this point you will be able to use your variable just as you did before.
I apologise if this isn't clear - I have a tendency to ramble!
Kindest Regards,
Frank Bazan
February 7, 2008 at 3:58 am
Just wanna say thank you. This worked perfectly & made my package much more simpler. I learnt about the Recordset Destination
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply