0

I've been developing a data pipeline in SSIS on an on-premise VM during my internship, and was tasked with gathering data from Marketo (re: https://www.marketo.com/ ). This package runs without error, starting with a Truncate table execute SQL task, followed by 5 data flow tasks that gather data from different sources within Marketo and moves them to staging tables within SQL Server, and concludes with an execute SQL task to load processing tables with only new data.

The problem I'm having: my project lead wants this process to be automated to run daily, and I have noticed tons of resources online that show automation of an SSIS package, but within my package, I have to have user input for the Marketo source. The Marketo source requires a user input of a time frame from which to gather data.

Is it possible to automate this package to run daily even with user input required? I was thinking there may be a way to increment the date value by one for the start and end dates (So start date could be 2018-07-01, and end date could be 2018-07-02, incrementing each day by one), to make the package run by itself. Thank you in advance for any help!

Chance Alexander
  • 1
  • 2

1 Answers1

1

As you are automating your extract, this suggests that you have a predefined schedule on which to pull that data. From this schedule, you should be able to work out your start and end dates based on the date that the package was run.

In SSIS there are numerous ways to achieve this depending on the data source and your connection methods. If you are using a script task, you can simply calculate the dates required using your .Net code. Another alternative would be to use calculated variables that return the result of an expression, such as:

DATEADD("Month", -1, GETDATE())

Assuming you schedule your extract to run on the first day of the month, the expression above would return the first day of the previous month.

iamdave
  • 12,023
  • 3
  • 24
  • 53
  • Thanks, I will probably do this but in the format of DATEADD("Day", -1, GETDATE()) to start with the prior day, and end at current day. Is there a way I can boost your profile or something besides upvoting? I just joined this site recently. – Chance Alexander Jul 18 '18 at 20:04
  • @ChanceAlexander You can upvote to show appreciation to a good question or answer and click "Accept" to mark this as the correct answer if it is. This highlights to other users with the same question that this was the solution that worked for you :) – iamdave Jul 19 '18 at 08:44
  • Gotcha. I did however run into an issue with the named variables. I'm trying to enter the variables (StartDate and EndDate) in the date parameters for the Marketo source, but Marketo won't accept them. I'm entering them as "User::StartDate" and "User::EndDate" am I using incorrect syntax? – Chance Alexander Jul 19 '18 at 13:39
  • How are you connecting to Marketo? What type of connection is it? You should be able to add an expression to your Marketo `ConnectionString` (or similar) property in which you can reference the variables to use the values they hold. – iamdave Jul 19 '18 at 14:24
  • I'm not entirely sure. When I arrived on the first day there was already a Marketo source in my SSIS package. All I can find in the connection manager is an identity, client ID, client secret, and rest endpoint. If it helps, we're using a third party (KingswaySoft) to get the data from Marketo. – Chance Alexander Jul 19 '18 at 15:16
  • Right click on the connection (as it appears in the package, not the Solution Explorer) and click properties. You should be able to see a row with the heading `Expressions` with a plus to the left. Hover over it, click the `...` ellipses and add an expression for the connection string. – iamdave Jul 19 '18 at 15:52
  • So I tried what you're suggesting, but I'm running into a new problem - I'm trying to add expressions `@StartDate` and `@EndDate` to the connection string, but I can't convert a date type to string type. Any ideas? – Chance Alexander Jul 23 '18 at 18:45
  • Just an update, I went to Kingswaysoft FAQ to figure out what exactly the requirements are for parameters. It will accept date values of mm-dd-yyyy , but the variable i'm using is mm/dd/yyyy. Any idea on how to change those backslashes to hyphens? – Chance Alexander Jul 23 '18 at 19:51
  • @ChanceAlexander You can use the `format` function in your expression, as demonstrated here: https://stackoverflow.com/questions/15975955/how-do-i-format-date-and-time-on-ssrs-report – iamdave Jul 24 '18 at 10:07