title: SSIS User Defined Date or Default
body: <p>I'm fairly new to SSIS and don't know all it's features and what tasks I can use to do things I want. I have found many Google and stackoverflow.com searches to help me get to know variables and parameters and how to set them etc.</p>
  
  <h1>BLUF (Bottom Line Up Front)</h1>
  
  <p>I have a <code>view</code> with data which I want to export to a file through a job that runs the package.</p>
  
  <p>The data will be filtered by it's <code>LastUpdatedDate</code> field with datatype of <code>DateTimeOffSet(7)</code>. The package should allow a user to run it with a specified dat or use a value from another table (<code>SSISJobRun</code>).</p>
  
  <h1>Structure</h1>
  
  <pre><code>/*Employee Table*/
  Id int
  Name varchar(255)
  LastUpdatedDate datetimeoffset(7)
  
  /*SSISJobRun Table*/
  Id int
  JobTypeId int
  RunDate datetimeoffset(7)
  </code></pre>
  
  <h1>What I have Done</h1>
  
  <p>Currently, I'm using SSDT for VS 2015 to create my SSIS packages and deploy them to my SSIS Catalog.</p>
  
  <p>I have a project with 1 package. The package contains:</p>
  
  <ul>
  <li>a <code>Data Flow Task</code> named <code>EmployeeExport</code>; this task contains an <code>OLE DB Source</code> and a <code>Flat File Destination</code></li>
  <li>a package level parameter named Filename_Export (this is so that the file path can be changed when it's run by a user; the parameter has a default value configured within the Job that runs it daily</li>
  </ul>
  
  <p><strong>All this runs perfectly fine.</strong></p>
  
  <h1>Problem</h1>
  
  <p>I also have set another package level parameter named <code>LastUpdatedDate</code>. The intent is to have who/what-ever runs the package to define a date. However, if the date is <code>null</code> (if I decide to use a <code>string</code>) or if the date is the default value <code>1899-12-30 00:00:00</code> (if I decide to use a <code>date</code>), I want to determine what date to use.</p>
  
  <p>Specifically, if there is no real date supplied by the user, then I want to the date to be the latest <code>RunDate</code>.  For that case I use the following code: </p>
  
  <pre><code>SELECT TOP 1 LastUpdatedDate 
  FROM SSISJobRun 
  WHERE JobTypeId = 1 
  ORDER BY LastUpdatedDate DESC
  </code></pre>
  
  <p>I've tried many different ways, and it works when I supply a date, but I couldn't get it to work when the date I gave was blank when I used a string or the default when I used a date.</p>
  
  <p>Here's a few sources I've been looking through to figure out my issue</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/36032225/how-to-pass-ssis-variables-in-odbc-sqlcommand-expression">How to pass SSIS variables in ODBC SQLCommand expression?</a></li>
  <li><a href="https://stackoverflow.com/questions/10217121/how-do-i-pass-system-variable-value-to-the-sql-statement-in-execute-sql-task">How do I pass system variable value to the SQL statement in Execute SQL task?</a></li>
  <li><a href="http://mindmajix.com/ssis/how-to-execute-stored-procedure-in-ssis-execute-sql-task" rel="nofollow noreferrer">http://mindmajix.com/ssis/how-to-execute-stored-procedure-in-ssis-execute-sql-task</a></li>
  </ul>
  
  <p>.. and many more.</p>
  
  <p>Once last note: this <code>date</code> will be used to run two tasks, so if there is a way to keep it global that would be great.</p>
  
  <p>Lastly, I need to package to insert a row specifying when the the task was run into the <code>SSISJobRun</code> table.</p>
  
  <p>Thank you.</p>