Si estás haciendo un paquete de integración con SSIS y tienes un destino SQL Server 2005 lo más lógico (a mí fue lo que me pareció) es usar el componente SQL Server Destination ya que, bueno, pensaba que mejor sería usar el suyo que uno más genérico. Y eso hice, usé SQL Server Destination. Y al ejecutar el workflow todo fue bien. Pero hay una frase que no saldrá de la ley de Murphy por mucho que nos empeñemos y esta frase es: "Si en prueba te funciona, en producción te fallará" y me falló, claro que me falló. Cambié la cadena de conexión del SQL Server 2005 de prueba que tengo en la misma máquina donde ejecuté el paquete SSIS a un SQL Server 2005 en otra máquina que es donde están los datos en producción para que la integración terminara metiendo los datos en la bd correspondiente y ahí empezó el problema.
Tras investigar, probar, buscar y preguntar llegué a una solución: usar OleDB como destino de los datos. Bueno, está bien, es una solución. Si tienes un SQL Server 2005 en una máquina que no es la que ejecuta el paquete SSIS usa un OleDB Destination y te funcionará, claro, con ese componente podrás usar gran variedad de bd como destino de la integración y funcionará.
Pero lo suyo sería usar SQL Server Destination no?. ¿Por qué no trabaja este componente cuando se trata de un SQL Server 2005 en otra máquina?. Si encuentro la solución a la pregunta la postearé aquí y si alguien la tiene y la quiere dejar agradecido estaré ;-).
Llegué a la misma conclusión, no usar SQL Server Destination, no sólo por lo que mencionás, sino que no funciona bien SQL Server Destination contra otros servidores diferentes del local.
Saludos.
Sólo trabaja en local (paquete y BBDD destino en el mismo servidor), y además sólo con SQL Server 2005. Todo esto «by design», vamos que es lo que hay :S