Solved

SSIS Pass Excel path variable from a script task to a data flow task

Posted on 2013-10-24
3
2,298 Views
Last Modified: 2016-02-11
Hi All,

I took this C# code from the internet which basically gets the latest file path I have in a directory.

 public void Main()
        {
            string[] files = System.IO.Directory.GetFiles(@"C:\SSIS\Files");
            System.IO.FileInfo finf;
            DateTime lastDate = new DateTime();
            string lastFile = string.Empty;
            foreach (string f in files)
            {
                finf = new System.IO.FileInfo(f);
                if (finf.CreationTime > lastDate)
                {
                    lastDate = finf.CreationTime;
                    lastFile = f;
                }
            }

            Dts.Variables["User::LastFile"].Value = lastFile;


            Dts.TaskResult = (int)ScriptResults.Success;
        }

Open in new window


I put the code in a Script Task and I was successfully able to get the path of the latest Excel file which is written to the [User::LastFile] variable.


However, I want to pass this variable to an Excel connection manager. I've done it through Expressions (in properties of the Excel connection manager)>ExcelFilePath property and chosen the variable as @[User::LastFile] variable.

When I setup an Excel Source Task and try to select the name of the Excel Sheet it says: Invalid argument.

Can anyone suggest how I can get my variable Excel full path successfully into an Excel connection manager to work in a Data Flow task so that I can eventually import that Excel file?

Thanks

OS
0
Comment
Question by:onesegun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39598068
The VB.NET script task would go like this..
Public Sub Main()
Dts.Connections("YourExcelFileConnection").ConnectionString = Dts.Variables("LastFile").Value
End Sub

Open in new window

Not sure on the C# script..
0
 

Accepted Solution

by:
onesegun earned 0 total points
ID: 39604564
Hi All,

I found this solution on the net and it works perfectly with my code.  A way to configure the  user variable that stores the Excel File path variable so that it can be used in a Data Flow Task. No further coding required.

http://dbaspot.com/sqlserver-dts/338698-sql-20005-ssis-dynamic-excel-path-connectionstring.html

Thanks,

OS
0
 

Author Closing Comment

by:onesegun
ID: 39616229
It's the only solution that works
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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