[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSIS DYNamic excel connection string in runtime

Posted on 2009-12-21
Medium Priority
Last Modified: 2013-11-10
I want to chnage excel connection string in run time?
how can i do it ,without package configuration
Question by:hp746
LVL 39

Expert Comment

by:Pratima Pharande
ID: 26102371
I will explain how I have done it
See if it helps you
efine variables
varProcessFolder : where Excel file resides
varFileName  : Excel file name
then in Script Component create the varExcelConnString  like below statement

    Dts.Variables("varExcelConnString").Value = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Dts.Variables("varProcessFolder").Value.ToString() & Dts.Variables("varFileName").Value.ToString() & ";Extended Properties=EXCEL 8.0;HDR=YES;"

And then assign varExcelConnString to the Excel file connection manager in Expression

Let me know if you have any question in this
LVL 30

Expert Comment

by:Reza Rad
ID: 26102750
Its better to use expression property of your excel connection manager
in expression select ConnectionString and set it to a variable of type string
then you can pass this variable from outside of package at runtime and connection string will change
LVL 30

Expert Comment

by:Reza Rad
ID: 26102767
look at this image for example

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 26104349
I know the expression.My problem is,I am reading registry values from the script task in the package and intilizing all the variables.But some how the connection string is  not updating

for example the path value of excel file is C:\myfile\test.xls.
all i am seeing \test.xls

so i need to intilize this path value during run time without package config.

Author Comment

ID: 26104387

do i need to write in preexcute of script component?

Author Comment

ID: 26104452
can we access DTs obejct in script component?
LVL 22

Accepted Solution

PedroCGD earned 2000 total points
ID: 26104497
You can create a SSIS variable that will be used in the connection expression.
Make sure you set the connection to DELAYVALIDATION
You can then call the package from a ASP.net page:
or from a filesystem...
or even other ways...

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question