Start and End Date Variables?

Sep 26, 2013 at 2:16 PM
Does anyone know if you can use variables for the start and end dates for this source? We'd like to set up a process to automatically pull in some data, but I don't want to have to change the dates manually every time.

Thanks!
Coordinator
Sep 26, 2013 at 2:28 PM
Hi,

You can use SSIS Expression to set the start and end date with variables.

Cheers,

Tillmann
Marked as answer by Tillmann on 2/15/2014 at 8:53 AM
Sep 26, 2013 at 2:33 PM
How would you do that? I'm somewhat new to SSIS. I don't see an area for Expressions in the GoogleAnalytics source. Thank you Jennifer Jennifer Jones, Certified Lotus Domino System Administrator Database Specialist AAA Ohio Auto Club 90 E. Wilson Bridge
Rd. Worthington, Ohio 43085 Phone: 614-431-7951 1-888-AAA-OHIO (222-6446) Ext 7951 Fax: 614-438-0540 [email removed] www.aaa.com "Committed to providing quality service and products to all members and clients"
Sep 27, 2013 at 4:58 PM
Jennifer,

Click on the canvas of the data flow, the properties -> +Expressions ... -> Property drop down and set the start and End Dates here.

John
Marked as answer by Tillmann on 2/15/2014 at 8:53 AM
Oct 30, 2013 at 1:34 AM
Hi I'm getting an error when I try to access the expressions "Element "[Google Analytics Source].[DefaultSegments] does not exist in collection "Properties". Any thoughts?

BTW, love the Data Source, exactly what I need to download data from several accounts.
Oct 30, 2013 at 7:32 PM
Edited Oct 30, 2013 at 7:33 PM
For anyone else who has this problem, I created a new Data Flow with a new instance of the data source and everything worked perfectly. Something got corrupted in the origianl Data Flow
May 19, 2014 at 6:32 PM
I am still having some challenges with this. I am at canvas>properties>propertyExpressionEditor>Expressions.

Almost regardless of what I put into the expression, when I click Evaluate Expression I get... "cannot convert "system.datetime' to 'system.string'

Any thoughts?
Does anyone have an expression they could share? Say one that would pick up "today" for the end date. Or start and end the previous day

Thank you

Tommy
May 19, 2014 at 6:40 PM
Tommy,
Below is what I am using. Hopefully it will help get you in the right direction. I am pulling "last month's" data so my start date evaluates to the first day of last month, and the end date evaluates to the last day of last month. Basically I found that you have to throw a "convert to string" command at the expression...

Start date:


End date:


Thank you
Jennifer




May 19, 2014 at 6:59 PM
It looks like the screenshots did not copy over, so here are the formulas:

Start Date (evaluates to 2014-04-01)
(DT_WSTR, 30)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-1,GETDATE()))

End Date (evaluates to 2014-04-30)
(DT_WSTR,30)(DT_DBDATE)DATEADD("D",-(DAY(GETDATE())),GETDATE())


Thank you
Jennifer





May 19, 2014 at 7:00 PM
Awesome
Thank you very much. That should get me going.
Thank you