1

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.

BLUF (Bottom Line Up Front)

I have a view with data which I want to export to a file through a job that runs the package.

The data will be filtered by it's LastUpdatedDate field with datatype of DateTimeOffSet(7). The package should allow a user to run it with a specified dat or use a value from another table (SSISJobRun).

Structure

/*Employee Table*/
Id int
Name varchar(255)
LastUpdatedDate datetimeoffset(7)

/*SSISJobRun Table*/
Id int
JobTypeId int
RunDate datetimeoffset(7)

What I have Done

Currently, I'm using SSDT for VS 2015 to create my SSIS packages and deploy them to my SSIS Catalog.

I have a project with 1 package. The package contains:

  • a Data Flow Task named EmployeeExport; this task contains an OLE DB Source and a Flat File Destination
  • 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

All this runs perfectly fine.

Problem

I also have set another package level parameter named LastUpdatedDate. The intent is to have who/what-ever runs the package to define a date. However, if the date is null (if I decide to use a string) or if the date is the default value 1899-12-30 00:00:00 (if I decide to use a date), I want to determine what date to use.

Specifically, if there is no real date supplied by the user, then I want to the date to be the latest RunDate. For that case I use the following code: 

SELECT TOP 1 LastUpdatedDate 
FROM SSISJobRun 
WHERE JobTypeId = 1 
ORDER BY LastUpdatedDate DESC

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.

Here's a few sources I've been looking through to figure out my issue

.. and many more.

Once last note: this date will be used to run two tasks, so if there is a way to keep it global that would be great.

Lastly, I need to package to insert a row specifying when the the task was run into the SSISJobRun table.

Thank you.

Community
  • 1
  • 1
RoLYroLLs
  • 3,113
  • 4
  • 38
  • 57
  • You can do this in SQL but you probably want to do this in SSIS. So use an _Expression Task_ to work out beforehand what the date value should be https://msdn.microsoft.com/en-us/library/hh213137.aspx – Nick.Mc Mar 16 '17 at 20:35
  • @Nick.McDermaid, i like this idea. I can use it at some point in the flow, like right after I do suggested by LONG http://stackoverflow.com/a/42843296/286618 – RoLYroLLs Mar 17 '17 at 19:16
  • @Nick.McDermaid Please add this as an answer so I can accept it along with LONG's answer since BOTH helped me out! – RoLYroLLs Mar 17 '17 at 19:44
  • Glad I helped. I'll only add an answer if I can explain it thoroughly. which I don't have time for right now – Nick.Mc Mar 18 '17 at 03:40

1 Answers1

2

Use a Execute SQL Task, paste

SELECT TOP 1 LastUpdatedDate 
FROM SSISJobRun 
WHERE JobTypeId = 1 
ORDER BY LastUpdatedDate DESC

in the statement, and set the result to single row, in the Result page, choose the variable you set, and change the index to 0

And before the same task run the 2nd time (inside any foreach or for loop) within the same execution and does not get used anywhere within the package, this variable will remain the same value.

if you need to check, right click that Execute SQL task, Edit Breakpoints to post execution, then run the package, open watch window from Debug tab, drag and drop the variable into watch window, you should see the value.

C B
  • 1,677
  • 6
  • 18
  • 20
LONG
  • 4,490
  • 2
  • 17
  • 35
  • Thanks for this. I'm currently working on it and I get an error when the result is empty which will happen on the first run of every package. I'll look for a solution while I wait for you to add one Thanks. – RoLYroLLs Mar 17 '17 at 19:14
  • I figured out I can set up the path from the to the next flow object as `Completion` vs `Success`. This allows an empty result set to allow the floe to continue to the `Expression Task` suggested by @Nick.McDermaid. Both answers helped me figure things out. Thanks! – RoLYroLLs Mar 17 '17 at 19:46