SSIS how to set connection string dynamically from a config file

by

First add a variable to your SSIS package (Package Scope) – I used FileName, OleRootFilePath, OleProperties, OleProvider. The type for each variable is “string”. Then I create a Configuration file (Select each variable – value) – populate the values in the configuration file – Eg: for OleProperties – Microsoft.ACE.OLEDB.12.0; for OleProperties – Excel 8.0;HDR=, OleRootFilePath – Your Excel file path, FileName – FileName

In the Connection manager – I then set the Properties-> Expressions-> Connection string expression dynamically eg:

"Provider=" + @[User::OleProvider] + "Data Source=" + @[User::OleRootFilePath]
+ @[User::FileName]  + ";Extended Properties=\"" + @[User::OleProperties] + "NO \""+";"

This way once you set the variables values and change it in your configuration file – the connection string will change dynamically – this helps especially in moving from development to production environments.

More Related Contents:

  1. Import most recent csv file to sql server in ssis
  2. How to create a temporary table in SSIS control flow task and then use it in data flow task?
  3. SSIS – How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename
  4. Parameterized OLEDB source query
  5. Microsoft.ACE.OLEDB.12.0 is not registered
  6. How to load a flat file with header and detail data into a database using SSIS package?
  7. Insert a single row and return its primary key
  8. How to read a flatfile with lowercase thorn as the delimiter
  9. Export Varbinary(max) column with ssis
  10. How to send the records from a table in an e-mail body using SSIS package?
  11. SSIS Package Not Running as 32bit in SQL Server 2012
  12. 2 Data Flow Tasks Linking to one Execute SQL Task
  13. The value violated the integrity constraints for the column
  14. How to convert string in format yyyyMMdd to date using SSIS expression?
  15. How to import a fixed width flat file into database using SSIS?
  16. How to replace double quotes in derived column transformation?
  17. SSIS 2012 – Insert new rows, ignore existing rows
  18. SSIS: script task (vs15) not work when deploy on sql server 2014
  19. How to connect to Oracle using Service Name instead of SID
  20. EntityFramework code-first custom connection string and migrations
  21. How to make an HTTP request from SSIS?
  22. How to read web.config file in .Net Core app
  23. SSIS Source Format Implicit Conversion for Datetime
  24. Modify config file using bash script
  25. What is the point of “Initial Catalog” in a SQL Server connection string?
  26. How to Map Input and Output Columns dynamically in SSIS?
  27. How to version control config files pragmatically?
  28. Is it .yaml or .yml?
  29. What are the differences between Merge Join and Lookup transformations in SSIS?
  30. Debugging SSIS Script task – Breakpoint is enabled but it does not hit

Leave a Comment