ETL package (ssis) does not load excel of 179 thousand records

Navigation
4

I have built an ETL package which loads data from an excel 2010 (179 thousand records) to a table in a database (simple construction, direct loading).

In my work there are three environments (Development, Quality and Production), my construction in the development environment is fully loaded through Jobs (Package and configs) and at project level (Visual Studio 2013).

In the environment of quality, of the same characteristics as development and production, Job also bears the excel in its entirety; but in the production environment with the same characteristics of the two environments mentioned above does not load data from the excel and indicates the following error:

  • Consider: GESTION_WT_VC (Excel file name) / Detail (Sheet name) / Weight of the excel file: 31 MB approx / Number of records: 179 thousand records.

  • Discards made in the production environment:

    • Change sheet with the same amount of data (same error).
    • Reduce the amount of data to 50 thousand records (same error).
    • The package contains flows 18 loads of excel with 2 thousand records approx. each and it is loaded satisfactorily except for GESTION_WT_VC (It is the heaviest excel).

  • The last thing I did with the excel file was to reduce the data to 5000 records and the Job was executed, obtaining a positive load response.

I do not know what could be happening in the production environment that does not allow loading the excel file with 179 thousand records. I remain attentive to your comments, thank you.

    
asked by JhonatanMR 26.02.2018 в 17:25
source

1 answer

4

We go in parts:

VS_ISBROKEN indicates that the component has an error that can be solved by the component in the designer. This error is typically caused by a custom property or requires a connection that is not specified or is incorrectly defined. You may have some numeric value and there is a spacing character somewhere.

  

One or more compontent failed validation

Here the solution is simple: 

Set DelayValidation = True  //En el administrador de conexiones y en el data flow.

The following error:

  

There were errors during task validation

This is due to a column mapping, check if there are no null fields in the destination.

In project properties RUN64bits to FALSE .

This gets interesting:

  

SSIS error code dts_e_oledberror. an ole db error has occurred. error code 0x80004005

Cause:

The owner of the job determines the security context in which the job runs. If the job is owned by a SQL Server Login account that is not a member of "Sysadmin fixed server role", the SSIS package runs in the context of "SQL Server Agent proxy account"

If you want the "SQL Server Agent proxy account" to run the jobs that connect to an instance of SQL Server, the SQL Server Agent proxy account must have the correct permissions of the instance of the SQL Server.

Solution:

You have to change the permissions for the Temp directory of the SQL Server Agent Service startup account. Grant read and write permissions to the SQL Server Agent Proxy account for this directory.

The "Rowset" beats me that is by some foreign key that is non-existent.

I hope it's useful for you, brother, have a good day, elevate the cosmos and give it all the power 2.0 !!!

    
answered by 26.02.2018 в 18:04
Android Studio Can the LogCat be set to colors? How to replace contents of an array with elements of another array JQUERY