I've developed a series of import scripts and packages to run the scripts. I've developed these using a test database which has the same table structures as the production database. I'm having trouble getting the packages to run against the production database.
After a sequence of attempts to make this work and trying different approaches our latest attempt was this morning and I've encountered something that has me stumped so that I don't know what else to try.
The import scripts have "SQL Before" code which clears the table being loaded as the file to be loaded is a complete replacement. The import script being run from the package successfully clears the table. However, when it tried to load the table with new data I got the following error:
Access violation at address 00887CEB in module 'VImpPro.exe'. Read of address 0000004C
This is similar to the errors I've gotten in the past, though the addresses change on different days. The addresses are the same for every table I attempt to run during a test. Thus I am clearing the table but then it is left blank (clearly unacceptable on a production system).
In an attempt to watch it run more closely as opposed to simply reviewing the execution monitor I tried running the import script manually. I was stunned when it ran successfully! I double checked that the file specified in the Advanced settings of the import module of my package is the same as the default file specified in the import script. Why would running the script from within a package work differently from running it directly using the little green "Run" arrow from the import edit screen?
One problem we've encountered before when loading these production tables using other methods (stored procedures) is trying to load the data while the user application is attempting to access the table caused problems. In order to work around this we now load a temporary table and then execute a SQL statement to insert into the production table from the temporary table. We have successfully loaded our test database while simulating user activity on the test database. Since these temporary tables are not used at all by the user application the import cannot be getting interference from the user application as it runs.
Note that the package which is attempting to load the table is being run as a module within another package. In fact, there are multiple levels of package definition. First I have a driver package which runs the entire process beginning with using ftp to get new files. Then the driver package invokes a series of packages, one for each state (AZ, CO, CT, GA, etc.). Each of these state specific packages run a sequence of packages, one for each table to be updated. It is while running these table specific packages that the import fails. Actually, in my test this morning I was running a state group so I could limit my test to just one state, so there was only one extra level of package above the table specific packages.
I don't know what else to try. When I saw my import fail when run from a package but succeed when run directly I don't see what the difference could be. I have run these successfully against the test database and there doesn't appear to be any difference between running the imports directly or from within a package. Do you have any ideas of what could be different between running the package and running the import directly?
