{"id":1864,"date":"2017-09-13T13:27:33","date_gmt":"2017-09-13T13:27:33","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1864"},"modified":"2025-05-13T07:55:18","modified_gmt":"2025-05-13T07:55:18","slug":"get-api-data-with-dynamic-url-and-load-into-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/","title":{"rendered":"How to make Path \/ URL dynamic in SSIS"},"content":{"rendered":"

Problem<\/h3>\n

\"\"<\/a>Most of SSIS PowerPack components support simple text placeholders engine<\/a> to make things dynamic without using Complex Steps of SSIS Expression Engine<\/a>. So let’s look at how to make Path \/ URL or other input values dynamic in SSIS. To show you how to do that we will use JSON Source<\/a> component, but basically, you can use it in any ZappySys PowerPack<\/a> component that has URL option, including XML Source<\/a>.<\/p>\n

 <\/p>\n

 <\/p>\n

Create a variable<\/h3>\n

\"\"<\/a><\/p>\n

Don’t forget to set the default value, because it will be used to construct an URL in design time. A call to that URL will be made to figure out the columns.<\/p>\n

Make an HTTP address dynamic<\/h3>\n

\"\"<\/a><\/p>\n

Creating a Dynamic Request Body<\/h3>\n
\nTo make a dynamic body<\/strong> in ZappySys<\/strong> (e.g., in an HTTP request or API call using the REST API Task<\/strong> or JSON Source<\/strong> or XML Source<\/strong>), you typically use placeholders or expressions<\/strong> in the request body.<\/p>\n

Here\u2019s how to create a dynamic body<\/strong>:<\/p>\n\n

Example: Dynamic JSON Body using Variables<\/strong><\/p>\n

{\r\n  \"name\": \"{{User::MyVariable}},\r\n  \"status\": \"active\"\r\n}<\/code><\/pre>\n
\n{{User::MyVariable}}<\/strong> is a variable placeholder.\n<\/p><\/blockquote>\n<\/div>\n
\"Dynamic<\/a>
Dynamic Body<\/p><\/div>\n
That’s How You Make It Dynamic<\/h3>\n
By combining placeholders<\/strong> and expressions<\/strong> in the URL and body fields, ZappySys lets you build fully dynamic API requests. Whether you\u2019re passing parameters, looping through records, or injecting runtime values\u2014this approach keeps your integration flexible, scalable, and efficient.<\/p>\n","protected":false},"excerpt":{"rendered":"
Problem Most of SSIS PowerPack components support simple text placeholders engine to make things dynamic without using Complex Steps of SSIS Expression Engine. So let’s look at how to make Path \/ URL or other input values dynamic in SSIS. To show you how to do that we will use JSON Source component, but basically, […]<\/p>\n","protected":false},"author":1,"featured_media":1871,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[238,236,13,237,235,239,100],"yoast_head":"\r\nHow to make Path \/ URL dynamic in SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to make Path \/ URL dynamic in SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2017-09-13T13:27:33+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-05-13T07:55:18+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"180\" \/>\r\n\t<meta property=\"og:image:height\" content=\"180\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/\",\"url\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/\",\"name\":\"How to make Path \/ URL dynamic in SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png\",\"datePublished\":\"2017-09-13T13:27:33+00:00\",\"dateModified\":\"2025-05-13T07:55:18+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png\",\"width\":180,\"height\":180},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to make Path \/ URL dynamic in SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to make Path \/ URL dynamic in SSIS | ZappySys Blog","description":"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to make Path \/ URL dynamic in SSIS | ZappySys Blog","og_description":"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.","og_url":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-09-13T13:27:33+00:00","article_modified_time":"2025-05-13T07:55:18+00:00","og_image":[{"width":180,"height":180,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/","url":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/","name":"How to make Path \/ URL dynamic in SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png","datePublished":"2017-09-13T13:27:33+00:00","dateModified":"2025-05-13T07:55:18+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to make URL dynamic to get data from REST\/SOAP API and load it into SQL Server using SSIS and ZappySys PowerPack.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/09\/load-data-from-api-to-sql-server-using-ssis-dynamic-variable-url-link-address.png","width":180,"height":180},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/get-api-data-with-dynamic-url-and-load-into-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to make Path \/ URL dynamic in SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1864"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=1864"}],"version-history":[{"count":32,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1864\/revisions"}],"predecessor-version":[{"id":11379,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1864\/revisions\/11379"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/1871"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=1864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=1864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=1864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}