Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Script Component Expand / Collapse
Posted Thursday, May 27, 2010 3:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 29, 2011 2:15 PM
Points: 9, Visits: 145
I am trying to get the file name with a for each loop container and a script component but I receive an error on the script component: Below is the script:

Public Sub Main()
Dim FileName As String
Dts.Variables("User::FileName").Value = System.IO.Path.GetFileName(Dts.Variables("User::FileName").Value.ToString())
Dts.TaskResult = Dts.Results.Success

End Sub

Post #929318
Posted Friday, May 28, 2010 2:01 AM



Group: General Forum Members
Last Login: Yesterday @ 2:09 AM
Points: 15,501, Visits: 13,163
What is the error that you get?

And why do you create a variable Filename that you don't use?

The following solution should work:

map the SSIS variable FileName in the For Each Loop to index 0 (see variable mappings).
Then, in your script component, use this code (make sure you add the variable FileName is a read-only variable in your script component/task):

Public Sub Main()
Dim FileName As String
FileName = Dts.Variables("User::FileName").Value.ToString()
Msgbox(Filename) 'To test it
Dts.TaskResult = Dts.Results.Success

End Sub

Note: if you use a script component, you should use Me.Variables.FileName to access the variable.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #929511
Posted Friday, May 28, 2010 4:47 AM



Group: General Forum Members
Last Login: Yesterday @ 10:56 PM
Points: 5,360, Visits: 7,068
From what you have stated, I presume you are running through a folder with numerous (1 or more) files to be imported.
when you create a foreach loop to iterate through a folder, you will have created a variable to hold the file name (eg @User::FileName).

thus, for each iteration, the variable is populated with the relevant file name being retrieved.
this variable can then be used in any subsequent process (constraint in the process flow, in a conditional split, added to the list of columns using a derived column component and captured in a table, etc.

the question to be answered therefore: what is it you need to achieve with the file name?

Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

  Post Attachments (12 views, 59.46 KB)
Post #929572
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse