Hello,

I have a ssis package which identifies duplicate records in access database. I have staged access database into sql sever and created ssis package. Now, I have final list of records which needs to be delete from access database and new records which are to be inserted into access database. 

What do I need to do if I want to delete those duplicate records directly from access database using SSIS. I cannot truncate whole access database and reload. I just have to delete duplicate rows from access db and add new records.

Thanks for suggestions.

You need to script it I found an example for you http://bomsbi.blogspot.ca/2012/08/ssis-delete-from-ms-access-tables.html

basically it is uses OLDB raw calls

I had looked at that blog once. Yes, that will help to delete all  rows from access database. But my requirement is, i have claim ID which are sql serer right now. I have to find those claim id in access and delete those records. Is there any way we can do that? or only the option is to truncate the access table and reload? 

Just elevate all the data from MS Access to SQL server, does the search and then once the ID is found go use that script task code to delete the specific record(s).

Yes,I staged the data in sql server and delete duplicate records. Now, I need to truncate the Access database table to load the data.  Is there any other way to truncate the access database table except using script components. I do not know how to write VB or C# scripts for Script component and I looked at the blog post, I could not find out what I need to replace on the codes posted .

Here was code to truncate or detele table from access using VB. Do you know what are the codes I need to change here.  I know I need to mention my table name here. what else I need to change in the code.

DO i need to change here:  Dim cm As ConnectionManager = Dts.Connections("AccessDB")

In place of "AccessDB" do I need to give my access database connection name in ssis package?

colTables = conn.GetSchema("Tables") 

Do I need to  Change "Tables" ?

   If Left(objTable.Item("Table_name").ToString, 4) <> "MSys" Then                 sql = "Delete from " & conn.DataSource.ToString & "." & objTable.Item("Table_Name").ToString

I believe I need to change "Table_name" to my access db table name which is called ALL.

Your help is appreciated.

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Data.OleDb Imports Microsoft.Office.Interop.Access


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults         Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success         Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure     End Enum


    Public Sub Main()
        Dim cm As ConnectionManager = Dts.Connections("AccessDB")         Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters100         Dim conn As OleDb.OleDbConnection

        cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters100)         conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
        If conn.State = ConnectionState.Closed Then             conn.Open()         End If
        'Get Tables From AccessDatabase         Dim colTables As DataTable         Dim objTable As DataRow         Dim sql As String         Dim cmd As OleDbCommand

        colTables = conn.GetSchema("Tables")
        For Each objTable In colTables.Rows
            If Left(objTable.Item("Table_name").ToString, 4) <> "MSys" Then                 sql = "Delete from " & conn.DataSource.ToString & "." & objTable.Item("Table_Name").ToString                 'MsgBox(sql)                 cmd = New OleDbCommand(sql, conn)                 cmd.ExecuteNonQuery()             End If         Next
        Dts.TaskResult = ScriptResults.Success     End Sub

End Class

It is a bit awkward that you have your stage database on a different server than your target database.  I generally stage the data on the same server as the target database.  This way, updates to the target database is just a simple query.

Hi 1234alex,

To delete a table from a Microsoft Office Access data source in SSIS, we need use the correct data provider based on the Access version to connect to the Access data source, then use the query below to delete the access table within a Execute SQL Task:
delete from table_name

For more information about how to connect to Access Database in SSIS, please refer to the following document:
https://msdn.microsoft.com/en-us/library/cc280478.aspx?f=255&MSPPError=-2147217396

Thanks,
Katherine Xiong

I tried both Microsoft Jet 4.0 OLE DB Provider and Microsoft Office 12.0 Access Database Engine OLE DB.  In both case I get this error. My table name is ALL.  

[Execute SQL Task] Error: Executing the query "DELETE FROM ALL" failed with the following error: "Syntax error in FROM clause.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have a location for share drive where access database is located. I can connect to  that share drive with out username and password. When I try to use useranme and password. I get error message 

"test connection failed because of an error in initializing provider .Cannot start your application.The workgroup information file is missing or opened by another user"

So, I have left the username and password blank. The file is not opened by another user.

Hi 1234alex,

After testing the issue in my environment, I can reproduce it when the table name is ALL.

Please note that ALL is a reserved keyword in SQL Server, so we cannot directly use it in SQL query. To fix this issue, please change your original query to like below:
DELETE FROM [ALL]

Reference:
Reserved Keywords (Transact-SQL)

Thanks,
Katherine Xiong

Thanks a lot.