Reading a Filename which has a Wildcard

Hi - I need to read a file but I do not know the whole filename!

For example, I need to read a file whose name is:- Finance_200510.xls

I know the prefix is "Finance" and I know the suffix is ".xls", but the bit in the middle could be anything. In the above example, it is a date which will vary from month to month.

I could achieve this as follows:-

1. I already need a .CMD file which contains a DTEXEC command.
2. So, I could insert the following line before the DTEXEC command:-
DIR Finance*.xls > ListFile.txt
(Alternatively, this command could be invoked from within the Package)
3. I could read the file: ListFile.txt using a SSIS Script Component
and extract the first occurrence of the string starting with "Finance"
and ending with ".xls" and save the filename to a package variable: @.[user::filename]
4. The package variable @.[user::filename] could be used in the expression property of a Connection Manager
5. The Connection Manager could then read the file.
6. Optionally, I could generate a RENAME command using a Script Component to rename Finance_200510.xls to Finance_200510.done so that it is not re-processed.

Can anybody suggest a simpler solution, please?!

Thanks.You can use multiFlatfile connection to provide file name with wildcard. In your case, you will have to make sure that there are no other unwanted files in that folder which might match the wildcard you are using. This will eliminate the need for listing all the files in the folder, and use script component to get the file whose name starts with "Finance". In your multiflatfile connection, you can just use Finance*.xls, and it will catch your needed file.

See BOL topic
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/31fc3f7a-d323-44f5-a907-1fa3de66631a.htm
for more on Multiple Flat File Connection Manager. The ideal use for Multiple flat file connection manager is to process more than one file which are of the same format (instead of using For Each Loop), but it will also solve your purpose of choosing file with Name that matches a wildcard.

HTH,
Ranjeeta Nanda|||Or similarly, you could use the Foreach Loop with the For Each File enumerator and a file pattern of "Finance*.xls."

If you really really wanted the exact name of the file before you started processing, or needed to check for other conditions, you could use the System.IO namespace within a Script Task to retrieve files that match the pattern, make sure there's only one, and pop the filename into a package variable for use by the next task.

-Doug

Read the filename, split it and put it in a table

Hello

I'm working on a package which loops through each xml file in a folder.
The name of each xml file is put in variable.
The format of the filename is something like "part1_part2_part3.xml"
I need to store the 3 parts in three different columns of table A
The content of the xml file needs to be manipulated ("." needs to be replaced with ",", ....)and put in serveral columns in tableB

It's not clear to me yet how to start this but my main concern is read the three parts of the filename. I don't find any task in SSIS which could help me with that.

Could someone give me some pointers?

Many thanks!

Worf

Since you alreday have the name of the file in a varibale; then create 3 extra variables and use the expression property for getting the part name. when looking into a variable press F4 to display the property panel and then change the property 'EvaluateAsExpression' to true; then you would have access to the expression builder. There are some string functions there.

Rafael Salas

|||how do you get the filename into a variable?|||

Cobr94 wrote:

how do you get the filename into a variable?

This a previous tread where i described something similar; I hope you can use it

Rafael Salas

sql

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?

Raw File Destination Access Mode Filename from Variable Problem

I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:\Test.txt.

When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\\Text.txt" ... so apparently XMLA is adding the \ escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.

What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.

The Watch window shows the C style encoded value, so \ becomes \\ in this display. Using a variable for the Raw File does work, and if setting a literal value it should just work. Have you perhaps got an expression on that variable? Could that confuse things?

Where does XMLA come into this?