SSIS随着脚本组件和服务引用 [英] SSIS With Script Component and Service References
本文介绍了SSIS随着脚本组件和服务引用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想通过SSIS获取BingAds数据在我们的会计系统来存储。我添加了一个服务引用的https：/ /api.sandbox.bingads.microsoft.com/Api/Advertiser/v8/CampaignManagement/CampaignManagementService.svc?wsdl 进行测试。
我已经复制：在GetCampaigns直接从 HTTP代码//msdn.microsoft.com/en-us/library/adcenter-campaign-management-csharp-samples-get-campaigns.aspx ，并略作修改，因为这不是一个控制台应用程序。
当我运行我的脚本组件，我得到以下信息：
信息
找不到，在ServiceModel
客户端配置部分引用合约
'BingAds.CampaignManagementService.ICampaignManagementService'默认端点元素。这可能是因为没有配置文件，发现你的应用程序
，或因为匹配这份合同
没有终结点元素可以在客户端的元素被发现。
我的app.config看起来应该有一个的所需要的一切。 ？我失去了一些东西。
我的app.config是如下：
<？XML版本=1.0编码=UTF-8>？;
<结构>
< system.serviceModel>
<&绑定GT;
<&basicHttpBinding的GT;
<绑定名称=BasicHttpBinding_ICampaignManagementServicecloseTimeout =00:01:00
openTimeout =00:01:00receiveTimeout =00:10:00的SendTimeout =00:01： 00
allowCookies =假bypassProxyOnLocal =假hostNameComparisonMode =StrongWildcard
MAXBUFFERSIZE =65536maxBufferPoolSize =524288maxReceivedMessageSize =65536
messageEncoding =TEXTtextEncoding =UTF-8transferMode =缓冲
useDefaultWebProxy =真正的>
< readerQuotas MAXDEPTH =32maxStringContentLength =8192maxArrayLength =16384
maxBytesPerRead =4096maxNameTableCharCount =16384/>
<安全模式=运输>
<运输clientCredentialType =无proxyCredentialType =无
境界=/>
<消息clientCredentialType =用户名algorithmSuite =默认/>
< /安全>
< /&结合GT;
< / basicHttpBinding的>
< /绑定>
<客户端>
<端点地址=https://api.sandbox.bingads.microsoft.com/Api/Advertiser/V8/CampaignManagement/CampaignManagementService.svc
结合=basicHttpBinding的bindingConfiguration =BasicHttpBinding_ICampaignManagementService
合同=BingAds.CampaignManagementService.ICampaignManagementService
NAME =BasicHttpBinding_ICampaignManagementService/>
< /客户>
< /system.serviceModel>
< /结构>
和我的脚本组件代码如下：
使用系统命名空间的#region
;
使用System.Data这;
使用Microsoft.SqlServer.Dts.Pipeline.Wrapper;
使用Microsoft.SqlServer.Dts.Runtime.Wrapper;
使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用System.Text;
使用System.ServiceModel;
：使用System.IO;
使用的System.Xml;使用System.Net
;
// @TODO这应该是一些更理智的;我们会到
使用SC_356d75396bc04171b425bdd1a48dd7b6.BingAds.CampaignManagementService;
#endregion
命名空间GetCampaignsByAccount
{
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
公共类ScriptMain：UserComponent
{
私有静态CampaignManagementServiceClient服务= NULL;
//私有静态StringBuilder的output_messages =新的StringBuilder（）;
私人静态列表<串GT;串=新的List<串GT;（）;
//指定您的凭据。
私人静态字符串m_password =;
私人静态字符串m_username =;
私人静态字符串m_token =;
//指定广告客户的帐户ID和用户ID。
私有静态长m_accountId = NULL;
私有静态长m_customerId = NULL;
//简单的例子来说明如何建立广告活动。
静态无效的主要（）
{
运动[]运动= NULL;
试
{
CampaignManagementServiceClient服务=新CampaignManagementServiceClient（）;
=运动GetCampaigns（m_customerId，m_accountId）;
//打印有关活动的信息。
如果（campaigns.Length大于0）{
方法addMessage（即帐户{0}包含以下活动，m_accountId）;
的foreach（在战役战役战役）{
方法addMessage（运动：{0}，campaign.Name）;
方法addMessage（ID：{0}，campaign.Id）;
//方法addMessage（状态：{0}，campaign.Status）;
方法addMessage（时间区：{0}，campaign.TimeZone）;
//方法addMessage（预算类型：{0}，campaign.BudgetType）;
如果（BudgetLimitType.MonthlyBudgetSpendUntilDepleted == campaign.BudgetType）
{
Console.WriteLine（每月预算：{0：C}，campaign.MonthlyBudget）;
}
，否则
{
Console.WriteLine（每日预算：{0：C}，campaign.DailyBudget）;
}
Console.WriteLine（）;
}
}
，否则
{
方法addMessage（即帐户{0}不包含活动。m_accountId）;
}
service.Close（）;
}
赶上（ē的CommunicationException）
{
方法addMessage（{0}，通信异常！）;
方法addMessage（{0}，e.Message）;
方法addMessage（{0}，e.StackTrace）;
如果（空= e.InnerException！）
{
方法addMessage（{0}，内部异常！）;
方法addMessage（{0}，e.InnerException.Message）;
方法addMessage（{0}，e.InnerException.StackTrace）;
}
如果（服务！= NULL）
{
service.Abort（）;
}
}
赶上（TimeoutException异常E）
{
方法addMessage（{0}，超时异常！）;
方法addMessage（{0}，e.Message）;
方法addMessage（{0}，e.StackTrace）;
如果（服务！= NULL）
{
service.Abort（）;
}
}
赶上（例外五）
{
//忽略我们已经抓住了故障异常。
如果（e.InnerException是FaultException异常）
{
;
}
，否则
{
方法addMessage（{0}，其他异常！）;
方法addMessage（{0}，e.Message）;
方法addMessage（{0}，e.StackTrace）;
}
如果（服务！= NULL）
{
service.Abort（）;
}
}
}
私有静态无效方法addMessage（字符串格式，字符串str）
{
字符串[]行= str中。斯普利特（新的String [] {} Environment.NewLine，StringSplitOptions.None）;
的foreach（在行字符串值）
{
串longVal =的String.Format（格式，值）;
strings.Add（longVal.Substring（0，Math.Min（longVal.Length，8000）））;
}
}
私有静态无效方法addMessage（字符串格式，长STR）
{
strings.Add（的String.Format（格式，STR） ）;
}
私有静态无效方法addMessage（字符串格式，长STR？）
{
strings.Add（的String.Format（格式，STR））;
}
静态运动[] GetCampaigns（长客户ID，长的accountId）
{
GetCampaignsByAccountIdRequest要求=新GetCampaignsByAccountIdRequest（）;
GetCampaignsByAccountIdResponse响应= NULL;
//设置首标信息。
request.CustomerId = customerId.ToString（）;
request.CustomerAccountId = accountId.ToString（）;
request.DeveloperToken = m_token;
request.UserName = m_username;
request.Password = m_password;
//设置请求的信息。
request.AccountId =帐户ID;
试
{
响应= service.GetCampaignsByAccountId（请求）;
}
赶上（FaultException异常< AdApiFaultDetail>故障）
{
//登录此故障。
strings.Add（GetCampaignsByAccountId与以下故障失败：\\\
）;
的foreach（在fault.Detail.Errors AdApiError错误）
{
如果（105 == error.Code）
{// invalidCredentials的
控制台.WriteLine（指定的凭据无效+
或该帐户是无效的。）;
}
，否则
{
Console.WriteLine（错误代码：{0}（{1}）\\\
Message：{2} \\\
Detail：{3} \\ \
，
error.ErrorCode，error.Code，返回Error.message，error.Detail）;
}
}
抛出新的异常（，故障）;
}
赶上（FaultException异常< ApiFaultDetail>故障）
{
//登录此故障。
Console.WriteLine（GetCampaignsByAccountId失败，出现以下故障：\\\
）;
的foreach（在fault.Detail.OperationErrors OperationError错误）
{
开关（error.Code）
{
案例106：// UserIsNotAuthorized
Console.WriteLine（该用户未被授权调用此操作。）;
中断;
案1030：// CampaignServiceAccountIdHasToBeSpecified
Console.WriteLine（以下简称CustomerAccountId头元素+
不能为空或为空）;
中断;
的情况下1102 // CampaignServiceInvalidAccountId
Console.WriteLine（帐户ID无效）;
中断;
默认：
Console.WriteLine（错误代码：{0}（{1}）\\\
Message：{2} \\\
Detail：{3} \\\
，
error.ErrorCode，error.Code，返回Error.message，error.Details）;
中断;
}
}
//这不是间歇操作，所以应该没有批次错误。
的foreach（在fault.Detail.BatchErrors查看BatchError错误）
{
Console.WriteLine（无法添加扩展＃{0}，error.Index）;
Console.WriteLine（错误代码：{0}（{1}）\\\
Message：{2} \\\
Detail：{3} \\\
，
error.ErrorCode，error.Code ，返回Error.message，error.Details）;
}
抛出新的异常（，故障）;
}
返回response.Campaigns;
}
///<总结>
///这种方法被称为一次，行开始在数据流进行处理之前。
///
///如果你不需要在这里做任何事情，您可以删除此方法。
///< /总结>
公共覆盖无效PreExecute（）
{
base.PreExecute（）;
}
///<总结>
///后的所有行通过该组件通过这种方法被调用。
///
///您可以删除此方法，如果你不需要在这里做任何事情。
///< /总结>
公共覆盖无效PostExecute（）
{
base.PostExecute（）;
}
公共覆盖无效CreateNewOutputRows（）
{
的Main（）;
的foreach（字符串中的字符串值）
{
MessagesBuffer.AddRow（）;
MessagesBuffer.Message =价值;
}
}
}
}
解决方案
的配置文件。NET看起来在加载时的配置数据是一个用于的实际运行可执行文件;添加的app.config 来的SSIS脚本组件的项目是不会产生SSIS知道要寻找一个配置文件。根据您选择如何运行你的包，它会寻找招标的配置文件，或者SQL Server或 DTEXEC 。
您会好得多构建端点代码和完全绕过配置文件。如果你的大部分配置选项默认它甚至不是那么复杂，因为你只需要设置更改的属性。
这堆栈溢出问题应该告诉你该怎么做它：
I'm trying to fetch BingAds data via SSIS to store in our accounting system. I've added a Service Reference to https://api.sandbox.bingads.microsoft.com/Api/Advertiser/v8/CampaignManagement/CampaignManagementService.svc?wsdl for testing.
I've copied the GetCampaigns code directly from http://msdn.microsoft.com/en-us/library/adcenter-campaign-management-csharp-samples-get-campaigns.aspx, and modified slightly, since this isn't a console application.
When I run my Script Component, I get the following message:
Message
Could not find default endpoint element that references contract
'BingAds.CampaignManagementService.ICampaignManagementService' in the ServiceModel
client configuration section. This might be because no configuration file was
found for your application, or because no endpoint element matching this contract
could be found in the client element.
My app.config looks like it should have everything that's needed. Am I missing something?
My app.config is below:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_ICampaignManagementService" closeTimeout="00:01:00"
openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
useDefaultWebProxy="true">
<readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
maxBytesPerRead="4096" maxNameTableCharCount="16384" />
<security mode="Transport">
<transport clientCredentialType="None" proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
</bindings>
<client>
<endpoint address="https://api.sandbox.bingads.microsoft.com/Api/Advertiser/V8/CampaignManagement/CampaignManagementService.svc"
binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_ICampaignManagementService"
contract="BingAds.CampaignManagementService.ICampaignManagementService"
name="BasicHttpBinding_ICampaignManagementService" />
</client>
</system.serviceModel>
</configuration>
And my script component code is below:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.IO;
using System.Xml;
using System.Net;
// @TODO this should be something more sane; we'll get to that
using SC_356d75396bc04171b425bdd1a48dd7b6.BingAds.CampaignManagementService;
#endregion
namespace GetCampaignsByAccount
{
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private static CampaignManagementServiceClient service = null;
// private static StringBuilder output_messages = new StringBuilder();
private static List<string> strings = new List<string>();
// Specify your credentials.
private static string m_password = "";
private static string m_username = "";
private static string m_token = "";
// Specify the advertiser's account ID and customer ID.
private static long m_accountId = null;
private static long m_customerId = null;
// Simple example that shows how to create a campaign.
static void Main()
{
Campaign[] campaigns = null;
try
{
CampaignManagementServiceClient service = new CampaignManagementServiceClient();
campaigns = GetCampaigns(m_customerId, m_accountId);
// Print information about the campaigns.
if (campaigns.Length > 0) {
AddMessage("Account {0} contains the following campaigns", m_accountId);
foreach (Campaign campaign in campaigns) {
AddMessage("Campaign: {0}", campaign.Name);
AddMessage("ID: {0}", campaign.Id);
// AddMessage("Status: {0}", campaign.Status);
AddMessage("Time zone: {0}", campaign.TimeZone);
// AddMessage("Budget type: {0}", campaign.BudgetType);
if (BudgetLimitType.MonthlyBudgetSpendUntilDepleted == campaign.BudgetType)
{
Console.WriteLine("Monthly budget: {0:C}", campaign.MonthlyBudget);
}
else
{
Console.WriteLine("Daily budget: {0:C}", campaign.DailyBudget);
}
Console.WriteLine();
}
}
else
{
AddMessage("Account {0} does not contain campaigns.", m_accountId);
}
service.Close();
}
catch (CommunicationException e)
{
AddMessage("{0}", "Communication Exception!");
AddMessage("{0}", e.Message);
AddMessage("{0}", e.StackTrace);
if (null != e.InnerException)
{
AddMessage("{0}", "Inner Exception!");
AddMessage("{0}", e.InnerException.Message);
AddMessage("{0}", e.InnerException.StackTrace);
}
if (service != null)
{
service.Abort();
}
}
catch (TimeoutException e)
{
AddMessage("{0}", "Timeout Exception!");
AddMessage("{0}", e.Message);
AddMessage("{0}", e.StackTrace);
if (service != null)
{
service.Abort();
}
}
catch (Exception e)
{
// Ignore fault exceptions that we already caught.
if (e.InnerException is FaultException)
{
;
}
else
{
AddMessage("{0}", "Other Exception!");
AddMessage("{0}", e.Message);
AddMessage("{0}", e.StackTrace);
}
if (service != null)
{
service.Abort();
}
}
}
private static void AddMessage(string format, string str)
{
string[] lines = str.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
foreach (string value in lines)
{
string longVal = String.Format(format, value);
strings.Add(longVal.Substring(0, Math.Min(longVal.Length, 8000)));
}
}
private static void AddMessage(string format, long str)
{
strings.Add(String.Format(format, str));
}
private static void AddMessage(string format, long? str)
{
strings.Add(String.Format(format, str));
}
static Campaign[] GetCampaigns(long customerId, long accountId)
{
GetCampaignsByAccountIdRequest request = new GetCampaignsByAccountIdRequest();
GetCampaignsByAccountIdResponse response = null;
// Set the header information.
request.CustomerId = customerId.ToString();
request.CustomerAccountId = accountId.ToString();
request.DeveloperToken = m_token;
request.UserName = m_username;
request.Password = m_password;
// Set the request information.
request.AccountId = accountId;
try
{
response = service.GetCampaignsByAccountId(request);
}
catch (FaultException<AdApiFaultDetail> fault)
{
// Log this fault.
strings.Add("GetCampaignsByAccountId failed with the following faults:\n");
foreach (AdApiError error in fault.Detail.Errors)
{
if (105 == error.Code)
{ // InvalidCredentials
Console.WriteLine("The specified credentials are not valid " +
"or the account is inactive.");
}
else
{
Console.WriteLine("Error code: {0} ({1})\nMessage: {2}\nDetail: {3}\n",
error.ErrorCode, error.Code, error.Message, error.Detail);
}
}
throw new Exception("", fault);
}
catch (FaultException<ApiFaultDetail> fault)
{
// Log this fault.
Console.WriteLine("GetCampaignsByAccountId failed with the following faults:\n");
foreach (OperationError error in fault.Detail.OperationErrors)
{
switch (error.Code)
{
case 106: // UserIsNotAuthorized
Console.WriteLine("The user is not authorized to call this operation.");
break;
case 1030: // CampaignServiceAccountIdHasToBeSpecified
Console.WriteLine("The CustomerAccountId header element " +
"cannot be null or empty.");
break;
case 1102: // CampaignServiceInvalidAccountId
Console.WriteLine("The account ID is not valid");
break;
default:
Console.WriteLine("Error code: {0} ({1})\nMessage: {2}\nDetail: {3}\n",
error.ErrorCode, error.Code, error.Message, error.Details);
break;
}
}
// This is not a batch operation, so there should be no batch errors.
foreach (BatchError error in fault.Detail.BatchErrors)
{
Console.WriteLine("Unable to add extension #{0}", error.Index);
Console.WriteLine("Error code: {0} ({1})\nMessage: {2}\nDetail: {3}\n",
error.ErrorCode, error.Code, error.Message, error.Details);
}
throw new Exception("", fault);
}
return response.Campaigns;
}
/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
/// </summary>
public override void PreExecute()
{
base.PreExecute();
}
/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
base.PostExecute();
}
public override void CreateNewOutputRows()
{
Main();
foreach (string value in strings)
{
MessagesBuffer.AddRow();
MessagesBuffer.Message = value;
}
}
}
}
解决方案
The configuration file that .NET looks for when it loads configuration data is the one for the executable that's actually running; adding an app.config to your SSIS Script Component's project isn't going to produce a configuration file that SSIS knows to look for. Depending on how you opt to run your packages, it's going to be looking for the configuration file for BIDS, or SQL Server, or dtexec.
You will be much better off constructing your endpoints in code and bypassing the configuration file entirely. If most of your configuration options are defaults it's not even that complex, as you only need to set the properties that changed.
This Stack Overflow Question should show you how to do it:
WCF Configuration without a config file
这篇关于SSIS随着脚本组件和服务引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
