source - Fuente de datos de Excel de SSIS: ¿es posible anular los tipos de datos de columna?

ssis excel 2016 (8)

Cuando se utiliza una fuente de datos de Excel en SSIS, los tipos de datos de cada columna individual se derivan de los datos en las columnas. ¿Es posible anular este comportamiento?

Idealmente, nos gustaría que cada columna entregada desde la fuente Excel sea un tipo de datos de cadena, de modo que la validación de datos se pueda realizar en los datos recibidos de la fuente en un paso posterior en el flujo de datos.

Actualmente, la pestaña Salida de error se puede utilizar para ignorar las fallas de conversión: los datos en cuestión son entonces nulos y el paquete continuará ejecutándose. Sin embargo, queremos saber cuáles eran los datos originales para poder generar un mensaje de error apropiado para esa fila.

Puede usar un componente de conversión de datos para convertir a los tipos de datos deseados.

Tuve problemas para implementar la solución aquí; podía seguir las instrucciones, pero solo daba nuevos errores.

Resolví mis problemas de conversión usando una entidad de conversión de datos. Esto se puede encontrar en la Caja de herramientas de SSIS en Transformaciones de flujo de datos. Puse la Conversión de Datos entre mi Fuente de Excel y OLE DB Destination, vinculé Excel a Data C, Data C a OLE DB, hice doble clic en Data C para mostrar una lista de las columnas de datos. Dio a la columna de problemas un nuevo Alias y cambió la columna Tipo de datos.

Por último, en las asignaciones del destino de OLE DB, use el nombre de la columna Alias, en lugar del nombre de columna original de Excel. Trabajo hecho.

Sí tu puedes. Simplemente vaya a la lista de columnas de salida en la fuente de Excel y establezca el tipo para cada una de las columnas.

Para acceder a la lista de columnas de entrada, haga clic con el botón derecho en la fuente de Excel, seleccione ''Mostrar Editor Avanzado'', haga clic en la pestaña etiquetada ''Propiedades de Entrada y Salida''.

Una solución potencialmente mejor es usar el componente de columna derivado en el que realmente puede construir columnas "nuevas" para cada columna en Excel. Esto tiene los beneficios de

Usted tiene más control sobre lo que convierte.
Puedes poner reglas que controlen el cambio (es decir, si null dame una cadena vacía, pero si hay datos, dame los datos como una cadena)
Su fuente de datos no está vinculada directamente con el resto del proceso (es decir, puede cambiar la fuente y el único lugar donde deberá realizar el trabajo es en la columna derivada)

Si su archivo Excel contiene un número en la columna en cuestión en la primera fila de datos, parece que el motor SSIS restablecerá el tipo a un tipo numérico. Siguió restableciendo el mío. Ingresé a mi archivo de Excel y cambié los números a "Números almacenados como texto" colocando una sola cita delante de ellos. Ahora se leen como texto.

También noté que SSIS utiliza la primera fila para IGNORAR lo que el programador indicó es el tipo real de los datos (incluso le dije a Excel que formatee toda la columna como TEXTO, pero SSIS todavía usaba los datos, que eran un montón de dígitos) y restablecerlo Una vez que lo arreglé poniendo una sola cita en mi archivo de Excel frente al número en la primera fila de datos, pensé que lo haría bien, pero no, hay trabajo adicional.

De hecho, aunque la Columna de fuente de datos externa SSIS ahora tiene el tipo DT_WSTR, todavía leerá 43567192 como 4.35671E + 007. Entonces debe volver a su archivo de Excel y poner comillas simples delante de todos los números.

¡Muy LAME, Microsoft! Pero ahí está tu solución. No tengo idea de qué hacer si el archivo Excel no está bajo su control.

La fuente de Excel es SSIS se vuelve loco. SSIS determina el tipo de datos en una columna particular leyendo las primeras 10 filas ... de ahí el problema. Si tiene una columna de texto con valores nulos en las primeras 10 huellas, SSIS toma el tipo de datos como Int. Con un poco de esfuerzo, aquí hay una solución

Inserte una fila ficticia (preferiblemente la primera fila) en la hoja de trabajo. Prefiero hacer esto a través de una tarea de Script, puede considerar usar algún servicio para preprocesar el archivo antes de que SSIS se conecte a él
Con la fila duummy, está seguro de que los tipos de datos se establecerán según lo necesite
Lea los datos usando la fuente de Excel y filtre la fila ficticia antes de tomarla para un procesamiento posterior.

Sé que está un poco gastado, pero funciona :)

Podría solucionar este problema. al crear el paquete SSIS, cambié manualmente la columna específica a texto (abra el archivo Excel, seleccione la columna, haga clic con el botón derecho en la columna, seleccione celdas de formato, en la pestaña numérica seleccione Texto y guarde el Excel). Ahora crea el paquete SSIS y pruébalo. Funciona. Ahora intente utilizar el archivo Excel donde esta columna no estaba configurada como texto.

Me funcionó y pude ejecutar el paquete con éxito.

De acuerdo con esta publicación del blog , el problema es que el controlador SSIS Excel determina el tipo de datos para cada columna según los valores de lectura de las primeras 8 filas:

Si los 8 registros superiores contienen la misma cantidad de tipos numéricos y de caracteres, entonces la prioridad es numérica
Si la mayoría de los 8 registros principales son numéricos, se asigna el tipo de datos como numérico y todos los valores de caracteres se leen como valores NULL
Si la mayoría de los 8 registros principales son de tipo carácter, entonces asigna el tipo de datos como cadena y todos los valores numéricos se leen como NULL

La publicación describe dos cosas que puedes hacer para solucionar esto:

Primero, agregue IMEX=1 al final de su cadena de conexión del controlador de Excel. Esto permitirá que Excel lea los valores como Unicode. Sin embargo, esto no es suficiente si los datos en las primeras 8 filas son numéricos.
En el registro, cambie el valor de HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Nod/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows a 0. Esto asegurará que el controlador HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Nod/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows todas las filas para determinar el tipo de datos para la columna.

Estaba buscando una solución para un problema similar, pero no encontré nada en Internet. Aunque la mayoría de las soluciones encontradas funcionan en tiempo de diseño, no funcionan cuando desea automatizar su paquete SSIS.

Resolví el problema y lo hice funcionar cambiando las propiedades de "Fuente de Excel". De forma predeterminada, la propiedad AccessMode se establece en OpenRowSet . Si lo cambia a SQL Command , puede escribir su propio SQL para convertir cualquier columna como lo desee.

Para mí, SSIS estaba tratando la columna NDCCode como flotante, pero la necesitaba como una cadena, así que utilicé el siguiente SQL:

Select [Site], Cstr([NDCCode]) as NDCCode From [Sheet1$]