SSIS DYNamic excel connection string in runtime

Posted on 2009-12-21
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
    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
    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
    look at this image for example


    Author Comment

    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


    do i need to write in preexcute of script component?

    Author Comment

    can we access DTs obejct in script component?
    LVL 22

    Accepted Solution

    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 page:
    or from a filesystem...
    or even other ways...

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now