CODEWITHSUNDEEP
excelssis
NJC59
NJC59

Reputation: 33

SSIS 2017 Loop Through Excel Files and Get FileName from Script Task

I’m stuck on the above.

Overview

I have created a SSIS package that loops through a folder of excel files, extracts data from within those files and inserts into a SQL Table.

Each day a new folder is created along the lines of 30 03 2020, 31 03 2020, 01 04 2020, etc.

My package deals with this and runs from start to end without a hitch.

I want however, to add the filename to each set of data within the target SQL table by way of a Derived column.

This is where I have a problem.

I can add the full file path and name, but I don’t seem to be able to extract the filename by itself.

Environment

I created a ForEachLoop container, wherein I set the Enumerator to “Foreach File Enumerator”, added in the full path of the Folder and set the files to ".xl"

Retrieve File Name is set to “Fully Qualified”

In the Variable Mappings I added in a variable called strFullFilePath, with an index of 0.

I then added in the Data Flow and most recently the Script Task.

From within the Data Flow, I dragged in an Excel Source.

I created the Excel Connection Manager by putting in the full path and file name of one of the files I wanted to load.

I set my Data access mode to SQL command from variable.

Having saved this, I set the Excel Connection Manager property to pass dynamic file name to excel connection string during run-time by adding in the @[User::strFullFilePath] variable to the ExcelFilePath Expression.

I finished up by creating an OLE DB SQL SERVER destination for my target table and mapped the fields.

The package ran without a problem and loaded all files until I added the Script Task and followed some of the suggestions I had seen on line.

The Script Task is currently configured as follows:-

Microsoft Visual Basic 2017

Entry Point:- Main

ReadWriteVariables:- User::strFullFilePath

When you edit the script, the code I have is:-

Public Sub Main()

    Dts.Variables("strFullFilePath").Value = System.IO.Path.GetFileName(Dts.Variables("strFullFilePath").Value.ToString())

    Dts.TaskResult = ScriptResults.Success

End Sub

When I run the package now, the Script Task seems to execute ok, but the main Excel file loading fails with a message of “[Excel Main Data Source [2]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.”

I’m not sure, but it would seem that the Package now thinks the File Path is the 0 that we entered in the For Each Loop Container.

I’ve tried a number of different scenarios, putting "User::" before the variable name in the script and also trying the following:-

Dts.Variables("strFileName").Value = System.IO.Path.GetFileName(Dts.Variables("strFullFilePath").Value.ToString())

But this just gives me a DTS Script Task: Runtime Error :- Exception has been thrown by the target of an invocation.

I am now completely stumped.

Any help/suggestions would be greatly appreciated.

Regards,

NJC

Upvotes: 2

Views: 1258

Answers (1)

NJC59
NJC59

Reputation: 33

I think I might have solved this.

I've added a Variable strFileName to the For Each Loop container under strFullFileName with an index of 1.

I've modified the VB script to:-

Dts.Variables("strFileName").Value = System.IO.Path.GetFileName(Dts.Variables("strFileName").Value.ToString())

and I can now see the filename in the Debug Locals window and post the results into a table.

Upvotes: 1

Related Questions