Script Component

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Script Component

Script Component

Rate Topic

Display Mode

Topic Options

Author

Message

lzlzgh

Posted Thursday, May 27, 2010 3:01 PM

Forum 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

Koen Verbeeck

Posted Friday, May 28, 2010 2:01 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376, Visits: 6,472

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

Stewart "Arturius" Campbell

Posted Friday, May 28, 2010 4:47 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 3,129, Visits: 4,312

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 (6 views, 59.46 KB)

Post #929572

« Prev Topic | Next Topic »

Permissions