Comportement aléatoire de SqlBulkCopy avec DbDataReader

asp.net excel sqlbulkcopy sql-server

Question

J'écris actuellement un site Asp.Net qui téléchargera un fichier Excel et enregistrera le contenu dans une base de données SQL Server, en utilisant SqlBulkCopy et un DbDataReader pour le faire.

Les colonnes d'Excel et les tables de la base de données portent exactement le même nom.

Code pertinent (nombre réduit de mappages de colonnes pour plus de clarté)

' Connection String to Excel Workbook
Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)
Dim connection As New OleDbConnection()
connection.ConnectionString = excelConnectionString
Dim command As New OleDbCommand("select * from [Sheet1$]", connection)

' Bulk Copy to SQL Server 
Dim bulkInsert As New SqlBulkCopy(sqlConnectionString)
bulkInsert.DestinationTableName = "UploadedOrders"
bulkInsert.BulkCopyTimeout = 0
bulkInsert.BatchSize = 50
bulkInsert.ColumnMappings.Add("Id", "Id")
bulkInsert.ColumnMappings.Add("CustomerId", "CustomerId")
bulkInsert.ColumnMappings.Add("Net", "Net")
... rest of columns

connection.Open()
Dim dr As DbDataReader = command.ExecuteReader()
bulkInsert.WriteToServer(dr)

Le dernier appel de WriteToServer génère une erreur en disant

La colonne 'Net' n'autorise pas DBNull.Value.

ce qu'il ne fait pas.

Cependant, il n'y a pas de null dans aucun de mes fichiers de test. Certains d'entre eux échouent et d'autres fonctionnent. Il se plaint de colonnes différentes pour des fichiers différents, mais toujours les mêmes pour un fichier spécifique.

Voici ce que j'ai essayé jusqu'à présent et les conclusions:

  1. J'ai divisé l'un de mes fichiers de test en plusieurs parties afin de tenter d'identifier une erreur de données. Un fichier plus petit fonctionne, alors j'ai ajouté de plus en plus de lignes à partir du fichier d'origine jusqu'à ce qu'il se casse, et c'est ce qui s'est passé. Cela indiquerait une erreur de données. Mais la ligne a l’air bien et en la plaçant dans un autre fichier, elle fonctionne. Et en le déplaçant à l'avant dernier et en supprimant la (maintenant) dernière ligne et cela fonctionne. Donc pas une erreur de données.

  2. L’idée suivante était la taille et lors de l’ajout des lignes une par une jusqu’à ce qu’elles se cassent, il existait un comportement reproductible: ajouter la ligne et le fichier ne fonctionnerait pas. Le retirer et ça marche. Et le nombre magique était 13959 lignes. Mais certains des fichiers qui fonctionnent sont plus gros, alors j’ai essayé un autre fichier qui ne fonctionnait pas. Il a montré exactement le même comportement, mais il a éclaté après 14259 lignes. Ce n’est donc pas un problème de taille, même s’il présente des problèmes liés à la taille. Répétable, mais différent pour différents fichiers. Et, encore une fois, d’autres fichiers plus volumineux fonctionnent.

  3. Essayé toutes sortes de délais d'expiration et de tailles importantes de SqlBulkCopy sans succès.

  4. Essayé toutes sortes de chaînes de fournisseur ADODB avec Excel 12, 8 avec ou sans XML, HDR, etc. Aucun changement.

  5. Essayé de trois packages différents de pilotes ACE (je suis sous Windows 10 64 bits et Office 2010 64 bits, il devrait donc y en avoir un correct, mais j'ai lu qu'il y avait quelques problèmes, j'ai donc essayé Office 2007 et 32 ​​bits également). Pas de changement.

  6. Ajout d'un code coupé pour lire le fichier ligne par ligne de DataReader et rechercher les lignes avec Net = Nothing au cas où il s'agirait d'un problème de lecture d'Excel plutôt que d'écriture dans la base de données. Je n'en ai trouvé aucun.

  7. A ajouté une question ici, en espérant que tout le monde puisse fournir quelques éléments supplémentaires à tester ou des indications sur ce qui se passe. Cela n'a pas de sens pour moi ...

Merci

METTRE À JOUR:

J'ai essayé d'ajouter tous les noms de colonnes dans Excel, comme suggéré par Gordon (merci!). N'a pas fonctionné

Nous avons également essayé d’ajouter un «où xx n’est pas nul» pour quelques paramètres générant des erreurs dans différents fichiers, comme suggéré par Bruce (merci!). Cela fait disparaître l'erreur et le fichier est téléchargé. Cependant, seule une partie du fichier est importée. Pour un fichier, 14088 enregistrements de 30727 ont été téléchargés dans le fichier et pour un autre fichier, 14305 enregistrements de 81195 ont été téléchargés.

Toujours pas de logique dans le nombre d'enregistrements, toujours pas d'erreurs de données apparentes ou de valeurs nulles dans Excel. Ça n'a pas de sens ...

Et toujours pas sûr si l'erreur se produit dans la lecture à partir d'Excel ou l'insertion dans la base de données.

Je viens également de trouver cela dans le journal des événements lors d'un téléchargement. Aucune idée si c'est lié.

"Une partie importante de la mémoire de processus du serveur SQL a été paginée. Cela pourrait entraîner une dégradation des performances. Durée: 0 secondes. Ensemble de travail (KB): 141460, engagé (KB): 393412, utilisation de la mémoire: 35 %%."

Réponse populaire

Ok, alors j'ai trouvé le problème et la solution.

Il s'avère que cela n'était pas lié à BulkCopy ou à la base de données. C'est la lecture d'Excel qui a faibli.

Après un peu plus de débogage et de code temporaire, j'ai constaté que seuls quelques enregistrements 14 'étaient lus à partir d'Excel dans DataReader.

Ajouté IMEX = 1 à la chaîne de connexion et nettoyé des guillemets doubles + triples et tout fonctionne. La chaîne de connexion d'as est maintenant:

"Fournisseur = Microsoft.ACE.OLEDB.12.0; Source de données = {0}; Propriétés étendues =" "Excel 8.0; HDR = OUI; IMEX = 1;" "", chemin d'accès)

Merci pour toutes les contributions. Très appréciée.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi