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

I am creating a new SSIS 2008 ETL report that will read data from a SQL Server and append it to an ACCESS 2010 database table. How can I append records to an existing access table when there is an identity column in the Access table?

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

My current solution has an OLE DB source with SQL that reads the data. It connects to a data conversion task and then that connects to an OLE DB destination task. The identity field in the access table is named "Id" and it is an autonumber field.

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

I am able to insert the values using an OLE DB destination when the Access table is empty. The problem has to do with the identity field and when there are already records in the table.

当Access表为空时，我能够使用OLE DB目标插入值。问题与身份字段有关，并且表中已有记录。

So, the problem is that my Id column always starts at 1, which means I get an error as the engine thinks I am trying to insert a duplicate key.

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

I created a SQL Task that reads the max ID from the table and stores it into a variable, but now I am stuck trying to figure out how to use this value in my data flow export process.

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

Will someone please show me how to insert data into an Access DB when there are already records in the table?

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

Edited for clarity. I have added the SQL that I use to obtain the desired SQL records below. Note that I am NOT including the ID identity field in my SQL:

编辑清晰。我添加了用于获取下面所需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

The results are linked to a Data Conversion Task.

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

The Data Conversion Task is then linked to an OLE DB Destination task.

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

The OLE DB Destination task uses an OLE DB Provider that connects to an Access Database. One of the Tables in this Database is called MasterTable and it has an autonumber field named ID.

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

I have added screenshots of the entire workflow below.

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

High-level data workflow
高级数据工作流程

OLE DB Destination connection info to Access Database
OLE DB目标连接信息到Access数据库

1 个解决方案

#1

I found a way to do what I need so I am posting the answer here in case it helps anyone else. I will summarize the steps below and then post the code. I am using SSIS 2008.

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

I derived this answer from a related answer I found on this site: What is the fastest way to insert 100 000 records into an MDB file in C#

我从本网站上找到的相关答案中得出了这个答案：在C＃中将100 000条记录插入MDB文件的最快方法是什么？

Create a data flow task

创建数据流任务
In the data flow task, create an OLE Db source with your SQL code. This is the query that will give you the results to put into your Access Table.

在数据流任务中，使用SQL代码创建OLE Db源。这个查询将为您提供放入Access表的结果。
Once I got the SQL working, I created a data conversion task and converted most of the columns to unicode (except the date columns). Pay attention to the names in the "Output Alias" column of the converter. These are the names you use in the C# script task shown below.

一旦我使用SQL，我就创建了一个数据转换任务，并将大多数列转换为unicode（日期列除外）。请注意转换器“输出别名”列中的名称。这些是您在下面显示的C＃脚本任务中使用的名称。
Save the results to a recordset destination. When you create the recordset, the "Component Properties" tab has a field named VariableName. Put a variable there. This variable will hold the results of the SQL query. I named mine "rsSourceTable." This variable is what the C# code will read to get our resultset.

将结果保存到记录集目标。创建记录集时，“组件属性”选项卡有一个名为VariableName的字段。在那里放一个变量。此变量将保存SQL查询的结果。我将我的名字命名为“rsSourceTable”。此变量是C＃代码读取以获取结果集的内容。
Once you get the Data Flow task working, create a C# Script Task. I created several variables for use with the script task.

完成数据流任务后，创建一个C＃脚本任务。我创建了几个用于脚本任务的变量。

The read-only variables:
AccessPath - Holds the path where the Access file is located.
rsSourceTable - the variable that holds the results of our data flow task.

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

The read/write variables: MasterTableRowCount - I use this to make it easy to report the number of files inserted in log files and email tasks.

读/写变量：MasterTableRowCount - 我使用它来轻松报告日志文件和电子邮件任务中插入的文件数。

6. The c# code for the script task is shown below. I did not have to add any references to this.

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
            }

        }
    }
}

#1