Variable value from flat file

Question

Post reply

Variable value from flat file

PHXHoward

SSCrazy Eights

Points: 8389
More actions
June 28, 2010 at 5:29 pm

#143838

Hi
Is it possible for a variable in SSIS to get it's value from a flat file?
I have a variable called ActiveSchema that I would like to hold the value of 1 or 2 based on what is written in the D:\SCHEMA.TXT file. There would either be a 1 or a 2 in the file.
Based on this variable's value, I can make precedence constraints to direct the flow to one or the other task.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 1

Read the txt file contents in script task using system.io, then you can define precedence constraints.

RB

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

PHXHoward

SSCrazy Eights

Points: 8389

June 28, 2010 at 11:05 pm

#1186152

Thanks for the suggestion RB.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

PHXHoward

SSCrazy Eights

Points: 8389

June 28, 2010 at 11:20 pm

#1186155

First try... updated below

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

I think that I'm getting close.

I made a system variable in SSIS that evaluates as an expression which is User::ActiveSchema

Than added this VB script task code:

Dim file As String

file = "D:\SCHEMA.TXT"

Dts.Variables("ActiveSchema").Value = System.IO.File.ReadAllText(file)

Then set User::ActiveSchema as a read/write variable in the script task.

The package now debugs as green but I don't think that the value is reading from the text file because when I look at the variable in the system variables window, it does not show 1 which is in the text file.

Also after doing this, when setting up the precedence constraint as expression @ActiveSchema = "1", BIDS locks up when I click ok.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

When you look at the value of the variable, is that when your package is running or after it has just ran?

To check, you can also include a messagebox in your code simply showing the value of the variable.

Furthermore, the expression in your precedence constraint should be the following:

@ActiveSchema == "1"

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 6

Thanks da-zero. I see, == is used because both sides are equal to each other.

I'll build a msgbox to find out for sure.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 7

Thanks so much or the help. This task is working perfectly now!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

Glad to hear it all worked out fine 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Can I ask why you want to use a file to accomplish this?

CEWII

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 10

I'm taking a DTS package and converting it to SSIS. The original package used a flat file source. I think that they want to allow users who don't have access to the database to change the value in the file.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

Well I can see that, but what prevents the user from entering bogus values and breaking the process?

CEWII

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 12

We don't have any users who have access to the flat file. I guess we could easily bring the value into a table and eliminate an external point of failure.

Really I just wanted to attempt to do it so I could improve my SSIS skills. I have the read part working great but I can't get the variable to write to the text file at the end.

I think I need an if then else statement. If ActiveSchema == "1" then write 2 to the text file at the end else do the opposite.