Using ssis variables to define startdate and enddate

Nov 4, 2013 at 5:26 PM
Hi,

I have 2 SSIS variables (datetime) that defines:

Startdate:

(DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()) +1, DATEADD("m", -1, GETDATE()))

Enddate:

(DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE()))

The values are exactly what i need.

But when I set the variables on the data flow properties gives the following error:

[SSIS GoogleAnalyticsSource] Error: Google.GData.Client.GDataRequestException: Execution of request failed: ... The remote server returned an error: (400) Bad Request.

Can anyone help me? :)

Regards,
Dec 17, 2013 at 7:43 PM
Using a script task, I created the following variables as ReadWriteVariables:

Dts.Variables("StartDate").Value = DateAdd(DateInterval.Day, -1, DateTime.Today).ToString("yyyy-MM-dd")
Dts.Variables("EndDate").Value = DateAdd(DateInterval.Day, -1, DateTime.Today).ToString("yyyy-MM-dd")

You'll then need to setup expressions for the [GoogleAnalyticsSource].[EndDate] and [GoogleAnalyticsSource].[StartDate]
Marked as answer by Tillmann on 2/15/2014 at 8:52 AM
Dec 12, 2014 at 3:44 PM
I did as it was advised by thisIsSteve and placed both statement in ReadWriteVariables property of GAS but when I replaced Start Date and End Date properties with statement as KaosBoss1977 suggested I got an error indicating I am using string in DateTime field. What did I do wrong? Please advise.
Dec 16, 2014 at 1:55 PM
Here are the code I am using in Script Task to change Start and End Dates variable:

If Dts.Variables.Count <> 0 Then
 Dts.Variables.Unlock()
End If
Dts.Variables("StartDate").Value = DateAdd(DateInterval.Day, -1, DateTime.Today).ToString("yyyy-MM-dd")
Dts.Variables("EndDate").Value = DateAdd(DateInterval.Day, -1, DateTime.Today).ToString("yyyy-MM-dd")
Dts.TaskResult = ScriptResults.Success

It through all bunch of errors on me. I cannot figure out why. Can someone advise?
Dec 16, 2014 at 10:17 PM
What errors are you receiving? What language is your script task configured to use?

Did you configure the Expressions for End Date and Start Date to use the user defined variables from the script task? These need to be configured at the Data Flow Task level in the Properties of the Task.
Dec 17, 2014 at 10:25 AM
With your help and some changes, I managed to solve this issue :)

Create Var
• Scope: all DTSX file
• ReadOnly: False
• Data Type: String
• YYYY-MM-DD


DataFlow Task that have the plugin GoogleAnalytics:
  1. Propriedades
  2. Expressions
  3. Edit
Script Task

• Script Language: Microsoft Visual C# 2008
• ReadWriteVariables: add created vars


• Edit Script…
• In the file ScriptMain.cs, funtion Main() add the code to calculate dates
• In this example we have the dates of “the last day”

public void Main(){

Dts.Variables["GAstartdate"].Value = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd");
        //MessageBox.Show(Dts.Variables["GAstartdate"].Value.ToString());
Dts.Variables["GAenddate"].Value = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd");
        //MessageBox.Show(Dts.Variables["GAenddate"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;

}

End
The script must run before the load

If someone wants screenshots, just ask :)
Dec 17, 2014 at 8:22 PM
I am using VB for Script Task. And an error I received indicated that I am trying to assign string to the object.
Dec 18, 2014 at 2:55 PM
Thank you, Kaosboss1977 and thisIsSteve for your help. However the problem I am facing is not exactly that. When I defined User Defined Variables "StartDate" and "EndDate" I have no problem to assign the date to them. But HOW to assign Start Date and End Date to appropriate Google Analytics Source properties? I manually entered Start Date and End Date. But now I want the script to assign different dates. I am trying to do it through Script Task. Any suggestions?
Dec 18, 2014 at 3:08 PM
Edited Dec 18, 2014 at 3:09 PM
If I remove User defined "StartDate" and "EndDate" I will get an error. So the problem is to make the properties visible in Script Task. Any thoughts from Tillmann?
Dec 18, 2014 at 7:33 PM
I finally figure it out! And it is working!!! There is only one error I got:

"[SSIS GoogleAnalyticsSource] Error: System.OverflowException: Value was either too large or too small for an Int16.
at System.Convert.ToInt16(Int32 value)
at CallSite.Target(Closure , CallSite , Type , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at SSISComponents.GoogleAnalyticsSource.PreExecute()"

Any suggestions what is it error about and what should I do to fix the error? Thank you guy.

And to Tillmann: Danke!
Jan 5, 2015 at 4:38 PM
Trying to do a simple download and having issues to use StarDate & EndDate as variables. Would any one of you kindly upload a sample/example dtsx file that has Start & End dates as variables ? . Thank you.
Jan 14, 2015 at 3:47 PM
To ZeeSpree:

Do you need to run dwnload one time? If yes just create DataFlow Task. Double click it and drag and drop the component into your DataFlow. Double click component and in dialog box manually set your Start and End Dates.
Jan 20, 2015 at 3:56 PM
Thank you Burzhuin, I figured it now. I was able to parameter-ize the start date end date and locations of Metrics.xml & Dimensions.xml
Feb 4, 2015 at 10:31 PM
Edited Feb 4, 2015 at 10:54 PM
For the benefit of people who will attempt to parametrize the GA adapter while building an SSIS package that can deployed to a Production environment in future, here are a few tips on how to configure this (very Awesome!!) adapter:-
  1. This source component can be parametrized by using "expressions" in SSIS. Key point to note here is that the various configurations for the source adapter are NOT available under the properties tab of the source component itself but under the properties tab of the "Data Flow" task! I don't know why this is implemented this way (may be difficult for the original developer to code them under source component's properties itself?) but that's where you can access them.
  2. When specifically parametrizing the StartDate/EndDate for GA adapter, make sure that:-
    a) the SSIS parameters/variables you declare to hold these values are of datatype "String" and not
    "DateTime". Again, not sure why its this way. May be make a request to the dev to support the DateTime
    datatype.
    b) the date values you store in these variables/parameters are strictly in the "yyyy-MM-dd" format. That seems to be the only format (of a few I tried) that this component recognizes as valid. All other date formats will result in an error message that looks like this:-
"[SSIS GoogleAnalyticsSource] Error: The service analytics has thrown an exception: Google.GoogleApiException: Parameter validation failed for "start-date"
at Google.Apis.Requests.ClientServiceRequest1.AddParameters(RequestBuilder requestBuilder, ParameterCollection inputParameters)
at Google.Apis.Requests.ClientServiceRequest
1.CreateBuilder()
at Google.Apis.Requests.ClientServiceRequest`1.CreateRequest()
at SSISComponents.GoogleAnalyticsSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)"


I hope this helps.

Cheers!
Muqadder.
Feb 5, 2015 at 1:59 AM
MuqadderIqbal wrote:
For the benefit of people who will attempt to parametrize the GA adapter while building an SSIS package that can deployed to a Production environment in future, here are a few tips on how to configure this (very Awesome!!) adapter:-



I hope this helps.

Cheers!
Muqadder.
Thank you, this would help someone. This is what I exactly did and I did waste considerable amount of time figuring out why there are no configurable properties in the
actual GA source itself.

--ZeeSpree