I've got some code that I have used to pull Google Analytics data with a c# console application and it works great. Whenever I try to use that same code in an SSIS script task I get the error "Error deserializing JSON credential data.". I get the error when running locally and when deployed. I've got all the libraries added to the GAC and I'm using the same version libraries and .net Framework as the console app. Anyone have any ideas?
public void Main()
{
string SQL_Script = null;
string ErrorMessage = string.Empty;
string ExceptionMessage = "No error";
// Declare the variables that you'll be pulling from Google Analytics into the database
DateTime GA_Session_Date = new DateTime();
DateTime GA_End_Date = new DateTime();
GA_End_Date = DateTime.Today.AddDays(-1);
string GA_TransactionId = null;
string GA_ChannelGrouping = null;
string GA_Source = null;
string GA_Medium = null;
string GA_Keyword = null;
string GA_Campaign = null;
string GA_Device_Category = null;
string GA_Region = null;
int GA_Transactions = 0;
/*
* Get the last SessionDate loaded
*/
GA_Session_Date = Convert.ToDateTime(GetMaxSessionnDate());
GA_Session_Date = GA_Session_Date.AddDays(-1);
/*
* Delete the last SessionDate loaded from the table
*
* The free version of Google Analytics takes up to 24 hours to bake
* so reloading the last day will ensure that we get all of the data.
*/
SQL_Script = "DELETE FROM OmniChannelAnalytics.GoogleAnalytics.Transactions WHERE SessionDate >= '" + GA_Session_Date.ToString() + "';";
ErrorMessage = ExecuteSQL(SQL_Script);
/*
* Create the DataTable and DataSet to house the data from GA until
* it is bulk loaded into SQL
*/
DataSet dataSet = new DataSet();
DataTable sessionTable = new DataTable();
sessionTable.TableName = "Sessions";
// Add the columns to the Sessions table
sessionTable.Columns.Add("SessionDate", typeof(string));
sessionTable.Columns.Add("TransactionId", typeof(string));
sessionTable.Columns.Add("ChannelGrouping", typeof(string));
sessionTable.Columns.Add("Source", typeof(string));
sessionTable.Columns.Add("Medium", typeof(string));
sessionTable.Columns.Add("Keyword", typeof(string));
sessionTable.Columns.Add("Campaign", typeof(string));
sessionTable.Columns.Add("DeviceCategory", typeof(string));
sessionTable.Columns.Add("Region", typeof(string));
sessionTable.Columns.Add("Transactions", typeof(int));
sessionTable.Columns.Add("LoadDate", typeof(string));
dataSet.Tables.Add(sessionTable);
while (GA_Session_Date <= GA_End_Date)
{
try
{
var credential = Google.Apis.Auth.OAuth2.GoogleCredential.FromFile(GlobalVariables.GA_ClientSecretFileLocation)
.CreateScoped(new[] { Google.Apis.AnalyticsReporting.v4.AnalyticsReportingService.Scope.AnalyticsReadonly });
using (var analytics = new Google.Apis.AnalyticsReporting.v4.AnalyticsReportingService(new Google.Apis.Services.BaseClientService.Initializer
{
HttpClientInitializer = credential
}))
{
var request = analytics.Reports.BatchGet(new GetReportsRequest
{
ReportRequests = new[] {
new ReportRequest{
DateRanges = new[] { new DateRange{ StartDate = GA_Session_Date.ToString("yyyy-MM-dd"), EndDate = GA_Session_Date.ToString("yyyy-MM-dd") } },
Dimensions = new[] {
new Dimension{ Name = "ga:transactionId" }
, new Dimension { Name = "ga:channelGrouping" }
, new Dimension { Name = "ga:sourceMedium" }
, new Dimension { Name = "ga:keyword" }
, new Dimension { Name = "ga:campaign" }
, new Dimension { Name = "ga:deviceCategory" }
, new Dimension { Name = "ga:region" }
},
Metrics = new[] { new Metric{ Expression = "ga:transactions", Alias = "Transactions"}},
ViewId = GlobalVariables.GA_View_ID
}
}
});
var response = request.Execute();
foreach (var row in response.Reports[0].Data.Rows)
{
GA_TransactionId = row.Dimensions[0];
GA_ChannelGrouping = row.Dimensions[1];
GA_Source = row.Dimensions[2].Substring(0, row.Dimensions[2].IndexOf("/")).Trim().Replace("'", "''");
GA_Medium = row.Dimensions[2].Substring(row.Dimensions[2].IndexOf("/") + 1, row.Dimensions[2].Length - row.Dimensions[2].IndexOf("/") - 1).Trim().Replace("'", "''");
GA_Keyword = row.Dimensions[3];
GA_Campaign = row.Dimensions[4];
GA_Device_Category = row.Dimensions[5];
GA_Region = row.Dimensions[6];
foreach (var metric in row.Metrics)
{
GA_Transactions = Convert.ToInt32(metric.Values[0]);
}
// Populate the data table to hold until everything is bulk loaded into SQL
DataRow newRow = sessionTable.NewRow();
newRow["SessionDate"] = GA_Session_Date;
newRow["TransactionId"] = GA_TransactionId;
newRow["ChannelGrouping"] = GA_ChannelGrouping;
newRow["Source"] = GA_Source;
newRow["Medium"] = GA_Medium;
newRow["Keyword"] = GA_Keyword;
newRow["Campaign"] = GA_Campaign;
newRow["DeviceCategory"] = GA_Device_Category;
newRow["Region"] = GA_Region;
newRow["Transactions"] = GA_Transactions;
newRow["LoadDate"] = DateTime.Now;
sessionTable.Rows.Add(newRow);
} // foreach (var row in rows)
}
} // try
catch (Exception ex)
{
ExceptionMessage = ex.Message;
}
finally
{
// Import the current day's Session data
foreach (DataTable table in dataSet.Tables)
{
ImportTable(table);
}
sessionTable.Clear();
}
// Iterate the session date to import by 1
GA_Session_Date = GA_Session_Date.AddDays(1);
} // while (GA_Session_Date <= GA_End_Date)
Dts.TaskResult = (int)ScriptResults.Success;
}
