raw file destination and environment variables

when using a raw file destination it would be nice to be able to use an environment variable for the filename property.

like

%my_extract%\data.txt

instead of

c:\my_extract\data.txt

Could you turn your screen round a bit... ...no, sorry, still cannot see it from here. Could you describe which OK button it is, any errors you may have when clicking, and perhaps briefly what lead up to this point.

|||

You could use the script task to define a SSIS variable, storing the file name, built using the environment variable. Then you can use this variable in Raw adapters by using the File Name from Variable access mode.

HTH.

|||

have you actually tried that?

|||

Now, I have. It worked fine for me.

Have you had problems with it?

|||How did you do it, there isn't an expressions setting for raw sources/destinations. What do you do to get the filename to be based on a variable?|||Raw file adapters have an AccessMode property, which allows you to enter a filename or a variable name. Variables can obviously use expressions.|||Doh, looked everywhere for that and ti was right under my nose.|||

not yet. I will give it a try today.

you should post some code and a sample usage.

|||

There is the AccessMode property on the raw adapters; change it to "File name from variable" and set your variable to the FileNameVariable property.

Thanks.

|||

Here is how I did it:

- Define RawFileName variable of type string on the package level

- Add the Script Task and set its ReadWriteVariables property to "RawFileName" and the script like this:

Public Sub Main()

Dts.Variables("RawFileName").Value = System.Environment.GetEnvironmentVariable("<your_env_var>") + "\<your_file_name>"

Dts.TaskResult = Dts.Results.Success

End Sub

- Add the Data Flow Task after the Script Task, define your data flow with the Raw File Destination and set its file name to come from the RawFileName variable, as explained in the previous messages.

HTH.

|||

not working for me.

so you actually ran it. it worked? seriously you actually ran it and it created the file you specified?

I create the variable at the package level.

I create the script task and stick the code in there.

I go to my advanced editor for raw file destination.

select accessmode: file name from variable

filenamevariable: user::rawfilename

it keeps telling me error at dataflow task (raw file destination[23]: the file name is not properly specified. supply the path and name to the raw file either directly in the file name property or by specifying a variable in the filenamevariable property.

what gives?

|||

Have you investigated why it is not working? If the file name is invalid, is the variable getting set correctly? Some ideas-

Set a breakpoint on the PreExecute event of the Data Flow and when broken drag the Rawfilename variable into the Watch window. Examine the value.

Add some breakpoints into the Script Task and examine the values as you step through the code.

|||the gui will not let me click the ok button|||

I can delete my raw file destination out of the data flow and put a break point on the data flow task and look at my variable.

it looks like a file name. correctly formatted in all it's glory.

user::RawFileName c:\\inetpub\\wwwroot\\a.txt

looks like a good file name to me?

why does the gui insist it's not a valid file name?