{"id":916,"date":"2014-12-11T06:07:58","date_gmt":"2014-12-11T06:07:58","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=916"},"modified":"2024-05-02T05:56:34","modified_gmt":"2024-05-02T05:56:34","slug":"use-of-dynamic-sql-for-ssis-data-sources","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/use-of-dynamic-sql-for-ssis-data-sources\/","title":{"rendered":"Use of Dynamic SQL for SSIS Data Sources"},"content":{"rendered":"<p>Quite a set of adventures in SSIS-land today.\u00a0 It seems using parameters on SQL Server Integration Services data sources can cause CAST errors.\u00a0 The only reason I was using parameterized OLE DB SQL Data Sources is to avoid a heavily nested SQL query that was performing poorly.<\/p>\n<p>Here\u2019s the CAST error that I was unable to solve no matter how many CASTs and CONVERTs I applied around the parameter:<\/p>\n<p>[OLE DB Source [219]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.<br role=\"presentation\" data-uw-rm-sr=\"\" \/>An OLE DB record is available. Source: \u201cMicrosoft SQL Server Native Client 11.0\u201d Hresult: 0x80040E21 Description: \u201cInvalid character value for cast specification\u201d.<\/p>\n<p>A good approach is to break down the queries, using a Control Flow Execute SQL Task, to populate a Variable.\u00a0 Note to populate a variable, one has to set the ResultSet to be a Single row.<\/p>\n<p>Next thing to be aware of is that the Variables window does not seem to show updated values. Ever.\u00a0 To see updated values, add a Script Task with a MessageBox in a scripttask; that\u2019s the way I roll, pop-ups during development all the way \ud83d\ude42<\/p>\n<pre lang=\"php\">MessageBox.Show(\"My favorite variable \" + Dts.Variables[\"User::TestVar1\"].Value.ToString());\r\n\r\n<\/pre>\n<p>Next thing is the Data Source can use a Variable as source for SQL, but the Data Source expects it to be sufficient SQL to compile.<\/p>\n<p>Happy SSISing!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Quite a set of adventures in SSIS-land today.\u00a0 It seems using parameters on SQL Server Integration Services data sources can cause CAST errors.\u00a0 The only reason I was using parameterized OLE DB SQL Data Sources is to avoid a heavily nested SQL query that was performing poorly. Here\u2019s the CAST error that I was unable [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":919,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[73,42],"tags":[],"class_list":["post-916","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics-and-reporting","category-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/916","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=916"}],"version-history":[{"count":7,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/916\/revisions"}],"predecessor-version":[{"id":928,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/916\/revisions\/928"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/919"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}