Hello,

I am trying to build a solution that extracts data from some large SQL
Server 2005 tables to MS Access databases on a regular scheduled basis. I
have built a prototype that pulls three tables from SQL and pushes them into
an Access 2003 database. This Access database will be used as a back end to
numerous Access apps.

In my solution, I first drop the Access tables before pushing them from SQL
to Access.

If a user has one of these tables opened from one of the Access applications
that links to it, the SSIS job ends immediately and reports success.

First, it seems like the SSIS job should fail and report a valid error
condition, something like "job failed, target was locked" or similar.

Second, can you recommend a better approach to getting lots of SQL tables
exported to MS Access 2003 on a regular scheduled basis given that fact that
users may have some of these Access tables opened from numerous Access apps?

FYI: the reason we are trying to extract SQL data for use by numerous Access
apps: our ERP system runs on SQL Server 2005 and warns us that our Access
apps may cause locking/blocking issues if we link the Access apps directly to
ERP SQL tables & views (even if we use (readuncommitted) table hints. Any
insight on this is greatly appreciated.

Thanks in advance.

--
Josh Blair (hfdev)
HydraForce, Inc.

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

I wanted to add that I used some drop table statements to some "Execute SQL
Task" items to the Control Flow before the Preparation SQL Task and the Data
Flow Task. I am not very well versed in SSIS, so please recommend better
techniques if you now of them.

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

One more thing...I just dug into the Job History in the Log File Viewer.
When I expand the log entry to see the details (the header and detail) log
entries both have a green check mark, but the details show the following:

Date 10/6/2008 12:06:53 PM
Log Job History (TestSQLAgentJob2ExportERPData2Access)

Step ID 1
Server INCSQL
Job Name TestSQLAgentJob2ExportERPData2Access
Step Name ExecuteMySSISExportPackage
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: INCSQL\SYSTEM. ...n 9.00.3042.00 for 32-bit Copyright (C)
Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:53 PM
Error: 2008-10-06 12:06:56.00 Code: 0xC002F210 Source: Execute SQL
Task Execute SQL Task Description: Executing the query "drop table
ERP_Item;" failed with the following error: "The database engine could not
lock table 'ERP_Item' because it is already in use by another person or
process.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly. End Error Error: 2008-10-06 12:06:56.00 Code:
0xC002F210 Source: Execute SQL Task 2 Execute SQL Task Description:
Executing the query "drop table v_BOM" failed with the following error:
"Table 'v_BOM' does not exist.". Possible failure reasons: Problems with the
query, "ResultSet" property not set correctly, parameters not set correct...
The package executed successf... The step succeeded.

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote: