SSIS 2008：将SQL数据导出到Access数据库2010表

问题描述：

我正在创建一个新的SSIS 2008 ETL报表，它将从SQL Server读取数据并将其附加到ACCESS 2010数据库表中。当Access表中存在标识列时，如何将记录附加到现有的访问表中？SSIS 2008：将SQL数据导出到Access数据库2010表

我目前的解决方案有一个SQL读取数据的OLE DB源。它连接到数据转换任务，然后连接到OLE DB目标任务。访问表中的标识字段被命名为“Id”，并且它是一个自动编号字段。

当Access表为空时，我可以使用OLE DB目标插入值。问题与身份字段以及表中已有记录的时间有关。

所以，问题是，我的Id列总是从1开始，这意味着我得到一个错误，因为引擎认为我想插入一个重复密钥。

我创建了一个SQL Task，它从表中读取最大ID并将其存储到一个变量中，但现在我试图弄清楚如何在数据流导出过程中使用此值。

有人请告诉我如何在表中已有记录时将数据插入到Access数据库中？

编辑的清晰度。我添加了下面用来获取所需SQL记录的SQL。请注意，我不包括我的SQL中的ID标识字段：

DECLARE @STARTDATE DATETIME = (SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,4,GETDATE()))-1), 
DATEADD(MM,4,GETDATE())),101)) 

DECLARE @ENDDATE DATETIME = (SELECT CONVERT(VARCHAR(25),DATEADD(DD,-(DAY(DATEADD(MM,5,GETDATE()))), 
DATEADD(MM,5,GETDATE())),101)) 

SELECT DISTINCT 
         ISNULL(CONVERT(VARCHAR(3),PP.LOBCD),'CPP') 'LOB_Name_Product' 
        , POLICYID 'Policy_#' 
        , P.PRODUCERID 'Agent_#' 
        , CONVERT(VARCHAR(14),POLICYEFFDT,101) 'Policy_Eff_Date' 
        , CONVERT(VARCHAR(14),TS.POLICYEXPDT,101) 'Policy_Exp_Date' 
        , CONVERT(NUMERIC(15,2),TS.TERMPREMAMT) 'Inforce_Prem_Sum' 
        , REPLACE(CONVERT(CHAR(100),REPLACE(REPLACE(N.INSUREDNM,CHAR(10),' '),CHAR(13),'')),',',' ') AS 'Insured_Name' 
        , REPLACE(P.PRODUCERNM1TX,',',' ') AS 'Agent_Name' 
        , PD.PREDSTATECD 'Policy_State' 
        , REPLACE(II.ADDRLINE1TX ,',',' ') AS 'Insured_Address_1' 
        , REPLACE(ISNULL(II.ADDRLINE2TX,''),',',' ') AS 'Insured_Address_2' 
        , II.CITYNM 'Insured_City' 
        , II.STATECD 'Insured_State' 
        , CASE WHEN LEN(RTRIM(II.ZIPCD)) > 5 THEN (SUBSTRING(II.ZIPCD,1,5) + '-' + SUBSTRING(II.ZIPCD,6,5)) 
         ELSE II.ZIPCD 
         END 'Insured_Zip' 
        , REPLACE(P.PRODUCERADDRLINE1TX,',',' ') AS 'Agent_Address_1' 
        , REPLACE(ISNULL(P.PRODUCERADDRLINE2TX,''),',',' ') AS 'Agent_Address_2' 
        , P.PRODUCERCITYNM 'Agent_City' 
        , P.STATECD 'Agent_State' 
        , CASE WHEN LEN(RTRIM(P.ZIPCD)) > 5 THEN SUBSTRING(RTRIM(P.ZIPCD),1,5) + '-' + SUBSTRING(RTRIM(P.ZIPCD),6,5) 
         ELSE P.ZIPCD 
         END 'Agent_Zip' 
        , CONVERT(VARCHAR(10), GETDATE(), 101) AS 'Upload_Date' 
        , 'Open' AS 'Status'      

FROM COPOLICYPOINTER PP 

JOIN COTRANSACTIONSUMMARY TS ON TS.SYSTEMASSIGNID = PP.SYSTEMASSIGNID 
AND TS.TRANSSEQNO = ( SELECT MAX(TRANSSEQNO) FROM COTRANSACTIONSUMMARY TS2 
                WHERE TS2.SYSTEMASSIGNID = TS.SYSTEMASSIGNID) 
AND TS.TRANSEFFDT = (  SELECT MAX(TRANSEFFDT) FROM COTRANSACTIONSUMMARY TS2 
                WHERE TS2.SYSTEMASSIGNID = TS.SYSTEMASSIGNID) 

JOIN COPOLICYDETAIL PD ON TS.SYSTEMASSIGNID = PD.SYSTEMASSIGNID 
AND PD.TRANSSEQNO = ( SELECT MAX(TRANSSEQNO) FROM COPRODUCER PD2 
                WHERE PD2.SYSTEMASSIGNID = PD.SYSTEMASSIGNID) 

JOIN COPRODUCER P ON P.SYSTEMASSIGNID = TS.SYSTEMASSIGNID 
AND P.TRANSSEQNO = ( SELECT MAX(TRANSSEQNO) FROM COPRODUCER P2 
               WHERE P2.SYSTEMASSIGNID = P.SYSTEMASSIGNID) 

JOIN COINSUREDNAME N ON N.SYSTEMASSIGNID = P.SYSTEMASSIGNID 
AND N.TRANSSEQNO = ( SELECT MAX(TRANSSEQNO) FROM COINSUREDNAME N2 
               WHERE N2.SYSTEMASSIGNID = N.SYSTEMASSIGNID) 

JOIN COINSUREDINFO II ON II.SYSTEMASSIGNID = N.SYSTEMASSIGNID 
AND II.TRANSSEQNO = ( SELECT MAX(TRANSSEQNO) FROM COINSUREDINFO I2 
               WHERE I2.SYSTEMASSIGNID = II.SYSTEMASSIGNID) 

WHERE TS.POLICYEXPDT BETWEEN @STARTDATE AND @ENDDATE 
AND PP.CANCEFFDT IS NULL 
AND PD.PREDSTATECD IN ('CT', 'RI', 'GA','NH','NY') 

ORDER BY POLICYID

结果与数据转换任务相关联。

数据转换任务然后链接到OLE DB目标任务。

OLE DB目标任务使用连接到Access数据库的OLE DB提供程序。这个数据库中的一个表称为MasterTable，它有一个名为ID的自动编号字段。

我添加了下面整个工作流程的屏幕截图。

高级别数据流

OLE DB目标连接信息来访问数据库

开始=>

我的理解是，关闭自动编号是不可能的。自动编号是Access中的一个字段类型，不像SQL Server中的整数列那样。 –

是的，先生，谢谢你的答复。但是必须有某种获取记录的方式，将当前标识值添加到我的SQL中，然后将记录附加到访问表中？这不可能是第一次出现这个问题。 :) – WorkJ

即使有所有的解释，目前还不清楚你想用自动编号字段做什么。 1）您不需要在INSERT（即附加）查询中指定自动编号字段。它将自动填入下一个有效的号码。 2）但是，如果您试图在该自动编号字段中输入特定值，那么为什么首先将其作为自动编号字段？如果值通过“转换任务”形成SQL Server，则将这些值插入标准数字字段（例如，非自动编号，长）。 –

答

我发现了一个方法来做我需要的东西，所以我在这里发布答案，以防其他人帮助。我将总结下面的步骤，然后发布代码。我使用SSIS 2008

我来源于相关的答案，我在此网站上发现这样的回答： 3210

创建一个数据流任务
在数据流任务，创建一个带有SQL代码的OLE Db源代码。这是可以将结果放入您的访问表的查询。
一旦我得到了SQL的工作，我创建了一个数据转换任务，并将大多数列转换为unicode（日期列除外）。请注意转换器“输出别名”列中的名称。这些是您在下面显示的C＃脚本任务中使用的名称。
将结果保存到记录集目的地。当您创建记录集时，“组件属性”选项卡具有名为VariableName的字段。在那里放一个变量。该变量将保存SQL查询的结果。我将它命名为“rsSourceTable”。这个变量是C＃代码读取的结果集。
一旦你得到数据流任务工作，创建一个C＃脚本任务。我创建了几个变量供脚本任务使用。

只读变量：
AccessPath - 保存Access文件所在的路径。
rsSourceTable - 保存我们数据流任务结果的变量。

读/写变量： MasterTableRowCount - 我使用它可以很容易地报告插入日志文件和电子邮件任务中的文件数量。

6.脚本任务的c＃代码如下所示。我不必添加任何对此的引用。

using System; 
using System.Data; 
using Microsoft.SqlServer.Dts.Runtime; 
using System.Windows.Forms; 
using System.Data.OleDb; 

namespace ST_afd8e3cca5534e51ba5855e82f502e92.csproj 
{ 
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] 
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase 
    { 

     #region VSTA generated code 
     enum ScriptResults 
     { 
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, 
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure 
     }; 
     #endregion 

     public void Main() 
     { 
      OleDbConnection myConnection = new OleDbConnection(); 
      try 
      { 
       string accessPath = Dts.Variables["AccessPath"].Value.ToString(); 
       string materTableId = Dts.Variables["MasterTableId"].Value.ToString(); 
       myConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + accessPath; 
       DataTable dt = new DataTable(); 
       OleDbDataAdapter adapter = new OleDbDataAdapter(); 
       adapter.Fill(dt, Dts.Variables["User::rsSourceTable"].Value); 
       int rowCount = 0; 
       if (dt.Rows.Count > 0) 
       { 
        rowCount = dt.Rows.Count; 
        Dts.Variables["MasterTableRowCount"].Value.ToString(); 
        myConnection.Open(); 
        //1. When building the INSERT statement, remember to enclose column names in square brackets. This prevents errors because Access allows special characters in column names and OLE DB doesn't 
        //2. Also remember that the order the column names appear in the INSERT statement is important for the code that adds parameters below. 
        //3. To prevent an error, the INSERT statement is first constructed with a ? for each parameter. The parameter is replaced with the 
        // appropriate column name in the for loop below. 
        string insertString = "INSERT INTO MasterTable ([LOB_Name_Product], [Policy_#], [Policy_State], [Policy_Eff_Date], [Policy_Exp_Date], [Insured_Name], [Insured_Address_1], "; 
        insertString += "[Insured_Address_2], [Insured_City], [Agent_#], [Agent_Name], [Inforce_Prem_Sum], [Status], [Upload_date], [Insured_Zip], [Insured_State], [Agent_Address_1], [Agent_Address_2], [Agent_City], [Agent_Zip], [Agent_State])"; 
        insertString += " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 
        OleDbCommand cmmd = new OleDbCommand(insertString, myConnection); 
        if (myConnection.State == ConnectionState.Open) 
        { 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //LOB_Name_Product 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Policy_# 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Policy_State 
         cmmd.Parameters.Add("?", OleDbType.DBDate, 10);  //Policy_Eff_Date 
         cmmd.Parameters.Add("?", OleDbType.DBDate, 10);  //Policy_Exp_Date 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_Name 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_Address_1 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_Address_2 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_City 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_# 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_Name 
         cmmd.Parameters.Add("?", OleDbType.Currency, 255); //Inforce_Prem_Sum 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Status 
         cmmd.Parameters.Add("?", OleDbType.Date, 10);  //Upload_date 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_Zip 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Insured_State 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_Address_1 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_Address_2 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_City 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_Zip 
         cmmd.Parameters.Add("?", OleDbType.VarWChar, 255); //Agent_State 
         cmmd.Prepare(); 
         OleDbTransaction trans = myConnection.BeginTransaction(); 
         cmmd.Transaction = trans; 
         foreach(DataRow dr in dt.Rows) 
         { 
          cmmd.Parameters[0].Value = dr["Copy of LOB_Name_Product"]; 
          cmmd.Parameters[1].Value = dr["Copy of Policy_#"]; 
          cmmd.Parameters[2].Value = dr["Copy of Policy_State"]; 
          cmmd.Parameters[3].Value = dr["Policy_Eff_Date"]; 
          cmmd.Parameters[4].Value = dr["Policy_Exp_Date"]; 
          cmmd.Parameters[5].Value = dr["Copy of Insured_Name"]; 
          cmmd.Parameters[6].Value = dr["Copy of Insured_Address_1"]; 
          cmmd.Parameters[7].Value = dr["Copy of Insured_Address_2"]; 
          cmmd.Parameters[8].Value = dr["Copy of Insured_City"]; 
          cmmd.Parameters[9].Value = dr["Copy of Agent_#"]; 
          cmmd.Parameters[10].Value = dr["Copy of Agent_Name"]; 
          cmmd.Parameters[11].Value = dr["Copy of Inforce_Prem_Sum"]; 
          cmmd.Parameters[12].Value = "Open"; 
          cmmd.Parameters[13].Value = DateTime.Today.ToString("d"); 
          cmmd.Parameters[14].Value = dr["Copy of Insured_Zip"]; 
          cmmd.Parameters[15].Value = dr["Copy of Insured_State"]; 
          cmmd.Parameters[16].Value = dr["Copy of Agent_Address_1"]; 
          cmmd.Parameters[17].Value = dr["Copy of Agent_Address_2"]; 
          cmmd.Parameters[18].Value = dr["Copy of Agent_City"]; 
          cmmd.Parameters[19].Value = dr["Copy of Agent_Zip"]; 
          cmmd.Parameters[20].Value = dr["Copy of Agent_State"]; 
          cmmd.ExecuteNonQuery(); 
         } 
         trans.Commit(); 
         myConnection.Close(); 
         Dts.TaskResult = (int)ScriptResults.Success; //add logging here for successful operation 
        } 
        else 
         Dts.TaskResult = (int)ScriptResults.Failure; 
       } 
       else 
        Dts.TaskResult = (int)ScriptResults.Success; //add logging here for no records 

      } 
      catch (OleDbException oleEx) 
      { 
       myConnection.Close(); 
       Dts.TaskResult = (int)ScriptResults.Failure; //add logging here for unable to connect 
      } 
      catch (Exception ex) 
      { 
       myConnection.Close(); 
       Dts.TaskResult = (int)ScriptResults.Failure; //add logging here for any other error 
      } 

     } 
    } 
}

SSIS 2008：将SQL数据导出到Access数据库2010表

相关推荐