Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script Component Expand / Collapse
Author
Message
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 13,251, Visits: 11,029
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 LessThanDot.

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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 4,015, Visits: 5,307
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 
ForEachLoopExample.zip (11 views, 59.46 KB)
Post #929572
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse