ms-access - hacer - formularios en access

¿Cómo obtengo programáticamente la lista de tablas de MS Access dentro de un paquete de SSIS?

Question

Heredé una base de datos de MS Access terriblemente escrita que necesito importar a SQL. La base de datos de Access tiene varias miles de tablas con definiciones de campo idénticas. Tengo cierta experiencia con SSIS, e importar una tabla es bastante simple.

Sin embargo, necesito crear un proceso donde pueda recorrer la lista de varios miles de tablas e importar cada tabla. Encontré esta declaración, que obtendrá una lista de todos los nombres de tabla en una base de datos de Access:

SELECT Name FROM MSysObjects WHERE (((MSysObjects.Type) = 1) AND ((Left ([Name], 4)) <> "MSys"));

Sin embargo, no estoy seguro de cómo usar esto (¿sintaxis de la tarea de script?). Creo que me gustaría hacer esto para completar una variable SSIS de tipo "objeto". De esa forma, puedo usar ForEach Loop para recorrer esta lista de tablas y realizar la importación. ¿Cómo puedo hacer esto? ¿O hay una mejor manera de recorrer cada tabla en la base de datos y realizar el mismo proceso?

Apreciaria cualquier sugerencia. ¡Gracias!

Answer 1

Podría poner los resultados de una tarea sql en una variable de tipo variable de objeto . Esa variable estará disponible para que la use en una tarea de bucle.

Dentro de su bucle for puede modificar el nombre de la tabla en la que está operando usando expresiones .

Después de un rápido vistazo, este artículo puede detallar la primera parte del proceso:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/

Answer 2

Como alguien con accesos de acceso, arreglaría los datos en Access primero (es decir, fusionando las múltiples tablas en tablas maestras) luego usaría el Asistente de migración de SQL Server para acceder a la conversión. Le permite simular la importación y corregir cualquier problema antes de que realmente lo haga.

El primer paso para fusionar las tablas de datos sería codificar en VBA, aunque probablemente tendría que crear algunas tablas con metadatos que mapeen qué se importa en qué (a menos que las tablas usen convenciones de nomenclatura que permitan que esto se determine algorítmicamente )

Answer 3

Esta es una forma posible de cargar datos de acceso en SQL Server, siempre que todas las tablas de Access tengan la misma estructura. Este ejemplo recorrerá las tablas en Access, a saber, Country y StateProvince . El paquete en este ejemplo creará estas dos tablas en SQL si no existen y luego las rellenará con datos de Access.

Proceso paso a paso:

1. Las tablas de acceso Country y StateProvince se muestran en las capturas de pantalla # 1 y # 2 .

2. En el paquete SSIS, cree dos conexiones OLE DB para conectarse a SQL Server y Access, como se muestra en la captura de pantalla n. ° 3 . Además, crea 3 variables como se muestra en la captura de pantalla # 4 . Las variables SelectQuery y TableName deben ser especificadas por una tabla válida en Access. Esto es necesario para la configuración inicial del paquete. Aquí en este caso, he elegido Country , que sí existe en Access.

3. Seleccione la variable SelectQuery y presione F4 para ver el panel de propiedades. En el panel Propiedades, establezca la propiedad EvaluateAsExpress en True y pegue la expresión "SELECT * FROM " + @[User::TableName] en la propiedad Expression . Esta expresión evaluará la tabla que se está bucleando actualmente. Referencia captura de pantalla n. ° 4

4. Las capturas de pantalla n. ° 5 y n. ° 6 muestran que las tablas dbo.Country y dbo.StateProvince no existen en SQL Server.

5. Configure la pestaña Control Flow del paquete SSIS como se muestra en la captura de pantalla n. ° 7 . Coloque una Script Task y conéctela a un Foreach Loop container . Dentro del contenedor, coloque una Execute SQL Task y una Data Flow Task .

6. Reemplace el código en la tarea de secuencia de comandos con el código proporcionado en la sección Código de tarea de secuencia de comandos. Este código hará un bucle en el esquema de acceso y solo buscará los nombres de la tabla. La lista de nombres de tabla se almacena en la variable de paquete AccessTables , que luego utilizará el Foreach loop container .

7. En la base de datos de SQL Server, cree un procedimiento almacenado denominado dbo.CreateTable utilizando la secuencia de comandos proporcionada en la sección de secuencias de comandos SQL . Este procedimiento almacenado creará una tabla en el servidor SQL si no existiera. Make sure that you alter the table schema defined in the stored procedure according to your needs.

8. Configure el Foreach loop container como se muestra en las capturas de pantalla # 8 y # 9 .

9. Configure la tarea Ejecutar SQL como se muestra en las capturas de pantalla # 10 y # 11 .

10. No podemos configurar la tarea de flujo de datos en este punto porque las tablas no existen en SQL Server. Entonces, ejecutaremos el paquete en este punto para que las estructuras de la tabla de acceso se creen en el servidor SQL. La captura de pantalla n. ° 12 muestra la ejecución del paquete de muestra. La captura de pantalla n. ° 13 muestra que las estructuras de la tabla se han creado en SQL Server pero aún no están pobladas con datos.

11. Ahora, configuraremos la Data Flow Task . Coloque un origen OLE DB Source y un OLE DB Destination dentro de la Tarea de flujo de datos. Conecte el origen OLE DB al destino OLE DB. Referencia captura de pantalla # 14 .

12. Configure el origen de OLE DB Source como se muestra en las capturas de pantalla # 15 y # 16 .

13. Configure el OLE DB Destination como se muestra en las capturas de pantalla # 17 y # 18 .

14. La captura de pantalla n. ° 19 muestra la ejecución del paquete de muestra dentro Data Flow Task .

15. La captura de pantalla # 20 muestra que las tablas de SQL Server ahora están pobladas con datos de las tablas de acceso.

Este ejemplo funcionará solo para tablas que tienen la misma estructura pero que difieren en el nombre. Si se agrega otra tabla llamada Employees al Acceso con solo las columnas Id y Name . Al ejecutar este paquete de ejemplo, se creará la misma tabla en SQL Server y también se rellenará con los datos.

Espero que ayude.

Scripts SQL:

CREATE PROCEDURE [dbo].[CreateTable]
(
    @TableName  VARCHAR(255)
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = ''IF NOT EXISTS ( SELECT  * 
                                FROM    sys.objects 
                                WHERE   object_id = OBJECT_ID(N''''[dbo].'' + @TableName + '''''') 
                                AND     type in (N''''U''''))
                    CREATE TABLE [dbo].'' + @TableName + ''(
                        [ID] [int] NOT NULL,
                        [Name] [nvarchar](255) NULL
                        ) ON [PRIMARY]''

    EXEC (@SQL)
END
GO

Código de tarea de script:

Código de C # que solo se puede usar en SSIS 2008 and above .

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

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

namespace ST_9b2714c55db14556be74ca92f345c4e3.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()
        {
            Variables varCollection = null;
            DataTable schemaTables = null;
            ArrayList tableNames = new ArrayList();

            Dts.VariableDispenser.LockForWrite("User::AccessTables");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            using (OleDbConnection connection = new OleDbConnection(Dts.Connections["AccessDB"].ConnectionString.ToString()))
            {
                string[] restrictions = new string[4];
                restrictions[3] = "Table";    
                connection.Open();
                schemaTables = connection.GetSchema("Tables", restrictions);
            }

            foreach (DataRow row in schemaTables.Rows)
            {
                foreach (DataColumn column in schemaTables.Columns)
                {
                    if (column.ColumnName.ToUpper() == "TABLE_NAME")
                    {
                        tableNames.Add(row[column].ToString());
                    }
                }
            }

            varCollection["User::AccessTables"].Value = tableNames;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Captura de pantalla n. ° 1:

Captura de pantalla n. ° 2:

Captura de pantalla n. ° 3:

Captura de pantalla n. ° 4:

Captura de pantalla n. ° 5:

Captura de pantalla n.º 6:

Captura de pantalla n. ° 7:

Captura de pantalla n. ° 8:

Captura de pantalla n. ° 9:

Captura de pantalla n. ° 10:

Captura de pantalla n. ° 11:

Captura de pantalla n.º 12:

Captura de pantalla n.º 13:

Captura de pantalla n.º 14:

Captura de pantalla n.º 15:

Captura de pantalla n.º 16:

Captura de pantalla n.º 17:

Captura de pantalla n.º 18:

Captura de pantalla n. ° 19:

Captura de pantalla n. ° 20: