locked
Need help using conditional date in OLE DB Source data flow task RRS feed

  • Question

  • I have a data flow task with an OLE DB Source (external SQL server with username/password) writing to an OLE DB Destination (our SQL server with windows authentication.) When I execute the package with the date hard-coded into the SQL command of the Source, it works great. I need to change this date to be a calculation based on the date the package is run. I need to pass in the 15th of the month or the last day of the month.  I have created a SQL statement which returns that value as needed that is:

    SELECT CASE 
    WHEN DAY(GETDATE()) < 16 THEN CONCAT(YEAR(GETDATE()),MONTH(DATEADD(MONTH,-1,GETDATE())),15)
    ELSE CONVERT(CHAR(8), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)), 112)
    END
    AS PAY_PERD_END_DATE

    I've tried passing the value as a variable/parameter set up from an Execute SQL task.  I have followed various tutorials that I have found on mapping the parameter.  I am not getting any errors but it is also not returning any rows.

    I need suggestions on how to fix this or change it so that I can accomplish having this SSIS package execute properly on any given day.  I welcome and appreciate any suggestions. 

    Wednesday, June 18, 2014 5:20 PM

Answers

  • set your direction to INPUT in your execute SQL Task. Also, if its a date, wouldn't you want the datatype to be DBTIME or DMTIMESTAMP. As well, check the data type of your variable, ensure they are the same in both the variable definition and parameter mapping
    • Marked as answer by mbackues Wednesday, June 18, 2014 8:02 PM
    Wednesday, June 18, 2014 7:54 PM
  • The source is irrelevant, this is a single SQL command that supposedly needs to populate the Pay_Perd_End_Date variable.

    Let's forget about the rest for now.

    Your query has no input, you expect output,

    now inspect the variable Pay_Perd_End_Date value.

    Can be done via the Locals window in SSDT/BIDS


    Arthur My Blog

    • Marked as answer by mbackues Wednesday, June 18, 2014 8:03 PM
    Wednesday, June 18, 2014 7:47 PM

All replies

  • Let me ask:

    This SQL statement result is what you need to capture to use in your package, do you?

    Then you need to map the resultset to a variable of execute SQL Task: http://msdn.microsoft.com/en-us/library/ms141689.aspx


    Arthur My Blog

    Wednesday, June 18, 2014 5:53 PM
  • have you checked to ensure that your variable is actually storing a value? use the below link to see how to create a breakpoint at time of execution to see what value is being stored in your variable

    http://agilebi.com/jwelch/2009/11/29/ssis-101-viewing-variable-values-at-runtime/

    Wednesday, June 18, 2014 6:26 PM
  • Yes, that is what I am trying.  In my control flow the first step is the Execute SQL Task with the sql stmt shown as above.  I have the Result Set as Single Row, SQL Source Type as Direct Input.  In Parameter Mapping I have it set to a package user variable called varEndDate with Direction set to Output and DataType as nvarchar, Parameter Name 0.  In Result Set, I wasn't sure what to put for the Result Name so I used Result Name = Pay_Perd_End_Date (that is the column name in step 2) and my user variable User::varEndDate.  The task executes successfully but I haven't yet found a way to see the value.

    I dragged the success constraint from this Execute SQL task to the Data Flow Task.  The data flow task consists of a OLE DB Source and an OLE DB Destination.  The OLE DB Source has a SQL statement using a ? for the parameter.  In the Set Query Parameter dialog I have tried using both 0 and Parameter0 in the Parameter.  The Variable is User::VarEndDate and the Param direction is Input.  Parse Query and Build Query both return the expected results when I plug in a value.  If I click Preview it says wrong number of Parameters, even though my SQL statement only has one ? in it, so I'm not sure what to do about that.

    The package executes successfully but says it writes 0 rows.  Do you think I have a variable/parameter not quite connected right?

    Wednesday, June 18, 2014 7:41 PM
  • Thank you for that link AJohnsonMO!  I was wondering how to do this.  I can see now that my Execute SQL Task is returning empty string.  Now I can work on that part of my package until I get a value. 
    Wednesday, June 18, 2014 7:46 PM
  • The source is irrelevant, this is a single SQL command that supposedly needs to populate the Pay_Perd_End_Date variable.

    Let's forget about the rest for now.

    Your query has no input, you expect output,

    now inspect the variable Pay_Perd_End_Date value.

    Can be done via the Locals window in SSDT/BIDS


    Arthur My Blog

    • Marked as answer by mbackues Wednesday, June 18, 2014 8:03 PM
    Wednesday, June 18, 2014 7:47 PM
  • I can see the value I expect when I do Build Query... on the Execute SQL Task ... maybe I don't have the Result Set Name right?
    Wednesday, June 18, 2014 7:53 PM
  • set your direction to INPUT in your execute SQL Task. Also, if its a date, wouldn't you want the datatype to be DBTIME or DMTIMESTAMP. As well, check the data type of your variable, ensure they are the same in both the variable definition and parameter mapping
    • Marked as answer by mbackues Wednesday, June 18, 2014 8:02 PM
    Wednesday, June 18, 2014 7:54 PM
  • Thank you both so much for your help today!  All your suggestions helped.  Making the Execute SQL Task Parameter be Input worked.  I thought it needed to be an output to the next step.

    I am so grateful!

    Wednesday, June 18, 2014 8:03 PM