FileName Variable not passing to System.IO.StreamReader

In SSIS I need to parameterize the file name instead of hard coding it in my Script Component. The below code works as expected with the hard coded value, however, when I define the variable as the same I get the following error:

The path is not of legal form

How do I assign the filename and path to the System.IO.StramReader?

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent
    Dim sr As System.IO.StreamReader

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        sr = New System.IO.StreamReader("C:\Users\sberna6\Documents\Visual Studio 2008\Projects\Conditional_Split\IDA_Pharmacy2.dat")
        'sr = New System.IO.StreamReader("User::FileName")

    End Sub
SharonBernalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron MalmsteadInformation Services ManagerCommented:
..not positive here, but I have had issues before.. with trying to access files directly in the project folder.

Instead I usually use (CurDir & "\" & FileName).. where the file would be placed in the debug or release folder.

Can you test that?

Otherwise your code looks fine to me.
It may be a Windows 7 thing, because I don't remember ever having that issue developing on XP Pro.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain for us in as non-technical terms as possible what you're trying to pull off here.
0
SharonBernalAuthor Commented:
Here's what I'm doing.
1. I have a variable assigned in my SSIS Script Component called USER::Filename which looks like the path below.

2. In my SSIS component I have a VB script that is using the System.IO.StreamReader

3. Issue: I can hard code the path as follows
sr = New System.IO.StreamReader("C:\Users\sberna6\Documents\Visual Studio 2008\Projects\Conditional_Split\IDA_Pharmacy2.dat")

4. When I switch the path to a variable, it fails.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharonBernalAuthor Commented:
When I execute the task with the variable I get the following error message:

"The path is not of a legal form"

Here's the full message:

   at System.IO.Path.NormalizePathFast(String path, Boolean fullCheck)

   at System.IO.Path.GetFullPathInternal(String path)

   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

   at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)

   at System.IO.StreamReader..ctor(String path, Boolean detectEncodingFromByteOrderMarks)

   at SC_1ca069343d964a67b4117085903e27f0.vbproj.ScriptMain.PreExecute()

   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()
0
SharonBernalAuthor Commented:
I discovered the issue was with variables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.