09 diciembre 2007

CTE Expressions en SSIS

Hace algunas semanas a mi compañera Elizabeth Córdova le sucedió algo bastante extraño al intentar usar “expresiones CTE” en uno de sus paquetes, pueden ver el siguiente link para ver más de CTE http://msdn2.microsoft.com/en-us/library/ms190766.aspx.

Lo extraño del caso es que logró usar CTEs en sus paquetes en el entorno de desarrollo pasando la validación en tiempo de diseño que hace SSIS sin problema alguno tal como se muestra en la siguiente imagen:




Pero como se ve en la siguiente imagen, esto no funcionaba cuando movía el paquete al entorno de producción donde el OLEDB Source Component daba el siguiente mensaje de error cuando se quería hacer una vista preliminar de la data obtenida por el query:


Como ven, el mensaje de error menciona un error de sintaxis con el mensaje “Statement could not be prepared””Incorrect sintax near the keyword with” y finalmente la solución sugerida que dice ”if this is a CTE the previous statement must be terminated with a semicolon”, o mejor dicho cierre la expresión anterior con un punto y coma (;).

Finalmente el problema se solucionó poniendo el famoso punto y coma al inicio de la expresión CTE, algo como “; WITH Temp (……” (thanks to Jamie Thomson for his help and fast response!). Como empecé este post, lo extraño del caso es que en el entorno de desarrollo el paquete pasa la validación sin el punto y coma, pero en producción sí lo requiere. Obviamente lo único que podemos pensar es en alguna configuración diferente entre ambos entornos. Ambos tienen SQL 2005 SP2 y Windows Server 2003 SP1. Las ediciones de SQL usadas son Development y Enterprise respectivamente. Las ediciones de Windows 2003 también son Standard y Enterprise respectivamente.

Alguien sabe a ciencia cierta la explicación detallada para este funcionamiento irregular??

Alan.

36 comentarios:

Priya Kannan dijo...

This information really worth saying, i think you are master of the content and thank you so much sharing that valuable information and get new skills after refer that post.
Python Training in Chennai

ram chinna dijo...

Best Mobility ServicesVery good informative article. Thanks for sharing such nice article, keep on up dating such good articles.

muthu dijo...

Nice blog...
Summer Course Training in Chennai | Summer Course Training in Madipakkam

desh dijo...

This is really great informative blog. Keep sharing.

Cloud services | Austere Technologies

desh dijo...

Nice blog with excellent information. Thank you, keep sharing.

Best Software company in USA | Austere Technologies

Avi dijo...

Great article, really very helpful content you made. Thank you, keep sharing.

Application Quality Management Services | Austere Technologies

AUSTERO dijo...

wow...nice blog, very helpful information. Thanks for sharing.

Digital Transformation Services | Austere Technology Solutions

AUSTERO dijo...

Very good informative article. Thanks for sharing such nice article, keep on updating such good articles.

Internet Of Things Services | IOT | Austere Technology Solutions

Steve M dijo...

MSBI is the newest powerful and useful tool used for business analysis. Thank you for sharing with us.

Python Training in Chennai
Python Training institute in Chennai

preethi Shetty dijo...

Needed to compose one little word yet thanks for the suggestions that you are contributed here...
Best Online Software Training Institute | Artificial Intelligence Training

sample dijo...

Great article, really very helpful content you made. Thank you, keep sharing.

Best Degree Colleges Hyderabad | Avinash College of Commerce

Austere dijo...

VERY INFORMATIVE BLOG. KEEP SHARING SUCH GOOD ARTICLES.

Best Commerce College in Hyderabad | Avinash College of Commerce

sandy dijo...

Thank you for sharing this valuable information. But get out of this busy life and find some peace with a beautiful trip book best Andaman honeymoon packages

Avinash dijo...

VERY INFORMATIVE BLOG. THANK YOU.

Best junior college in Hyderabad | Avinash College of commerce

Avinash dijo...

Good information.

chartered accountant coaching in Hyderabad | Avinash College of commerce

Avinash dijo...

Good information.

B.com Integrated Courses | Avinash College of commerce

Avinash dijo...

Really great blog, it's very helpful and has great knowledgeable information.

Best Blockchain services | Massil Technologies

Avinash dijo...

Really great information, keep sharing.

Best degree college in hyderabad

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best Block Chain Services

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best SYSTEM INTEGRATION SERVICES

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide Software Development Services

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide About Best Software Development Services

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best Finance Training in Hyderabad

sandy dijo...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Andaman Tourism

sandy dijo...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book Best Travel Agency In India

sandy dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best Association Of Chartered Certified Accountants

Unknown dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best Find All Isfs Courses

Unknown dijo...

Hi Thanks for the nice information its very useful to read your blog. We provide best Massil Technologies

Massil dijo...

Very interesting blog with excellent information.

ca coaching in hyderabad | Avinash college of commerce

Massil dijo...

Very informative blog, really helpful. Thank you.

cs course eligibility | ISFS

Avinash dijo...

Great informative blog, keep for sharing.

Best Blockchain services | Massil Technologies

Avinash dijo...

Wow...Excellent informative blog, really helpful. Thank you.

Best CMA Training in hyd | ISFS

Avinash dijo...

Excellent informative blog, keep for sharing.

Best System Integration services | Massil Technologies

Avinash dijo...

Wow...Excellent informative blog. Thank you.

Best CA Training in hyderabad | ISFS

Avinash dijo...

Best informative blog. Thank you.

cima courses in hyderabad | ISFS

Avinash dijo...

Your article gives lots of information to me. Thanks for sharing.
acca course in hyderabad | ISFS