[b]Using Wildcard for An Variable[/b] 1

[elinsd]

I have a task to run a package to check a flat file existence daily and if it exists then import the file into the database.

Since the file names look like this "abc_20081021_123456.dat", I can set a file name variable to find the file name with current date info and I used a wildcard for the part ("e.g. "*20081021*.dat") that I don't care but it didn't work.

What is the proper syntax for that? Is there any other alternatives to achive something like this?

Much appreciate!!

 

[MDXer]

id doing it as a variable you might need to escape the * try \*20081021\*.dat

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[elinsd]

Thanks Paul. I tried the escape "\*" as you mentioned but it was not supported in expression.

 

[elinsd]

I now tried to use script task to update the FileName variable and it worked the way I wanted but the wildcard still not working with the following VB.Net code. I hope someone can point the syntax mistake I made.

FilePart = Format(Now, "yyyy")& Format(Now, "mm") & Format(Now,"dd"
Dts.Variables("FileName").Value = "abc" & filePart& "*.dat"

I put a MsgBox after this code and it returned the fileName as "abc20081023*.dat"

I'm not sure this fileName has been passed on to the next task. How can I go beyond this to pull a file name such as "abc20081023_123456.dat" (the current date part can be set easily but the dynamic part of "123456" seems to be the problem. and I'm not sure it's been written back to the global variable and passed on to the following tasks.

Can any tell me how exactly this can be done programmatically? Many thanks in advance.

 

[EKOnerhime]

I've got this to work using 2 variables, a script task and a ForEach loop container.

Variable #1 - WildCard at package scope.
Variable #2 - FileName at package scope.

Create a Script Task and set this to the wildcard desired e.g.

        Dim FilePart As String

        FilePart = Format(Now, "yyyyMMdd")

        Dts.Variables("WildCard").Value = "abc" & FilePart & "*.date"

Create a ForEach loop container and connect the Script task to precede it.
In General: Set the folder to your folder name and ensure it is returning a fully qualified file name.

In Collection: In Foreach Loop Editor click the ellipses next to Expressions. For property select FileSpec, for expression select your variable @[User::WildCard]. Click OK.

In Variable Mappings: Select User::FileName, the index will be 0. Click OK to close.

Drag a Data Flow Task into the ForEach container.

In the Data Flow Task, create a Flat File Source. You will need an initial connection to one of your files. In the Flat file connection manager, click New and point to one of your files. Create the rest of your connection and destination as normal, then close the Flat File Source.

In Connection Manager click on the Source you just created. Click on the ellipses next to Expression. Select ConnectionString as the property and @[User::FileName] as the expression. Click OK.

What this should do is set the FileSpec property of the ForEach loop using the variable WildCard e.g. "abc20081110*.dat". This would already have been set to today's date (or whatever date you desire) using the Script Task.

When the ForEach loop finds the file(s), it will set the FileName property to the full path and name of each file it finds as it loops through.

Finally, the Source of the Flat File Source picks this up and resets the ConnectionString to the variable.

I'll look into uploading a zip file later on. This was created using BIDS on SQL 2005.

 

[elinsd]

It works!! Thank you so much, EKOnerhime. You ROCK!

 

[elinsd]

One more question on this.

I also need to capture the file name and write it to the table, right now I can only write out something like "abc_20081110*.dat" because the variable "WildCard" is set that way. How can I capture the real file name with the timestamp such as "abc_20081110_032015.dat"?

 

[EKOnerhime]

No problem, it's something I've worked on myself.

To get the File Name: In your Data Flow Task, you can use a Derived Column between your Flat File Source and your destination table. Use the Variables and drag the User::FileName expression to create a derived column. I used a DT_STR data type and a 50 character length which worked fine.

 

[elinsd]

Actually, I'm using exactly the same process to pass the file name to the table but since I set the file name in the Script Task as "abc_date*.dat" so after I drag and drop the User::FileName in the Derived Column's expression, that's what I currently got into the table. I think I need to do something additional to capture the full flat file name so that a "abc_date_time.dat" can be inserted to the table.

 

[EKOnerhime]

Ah I see, I used 2 variables to avoid that, it's not a great overhead.

 

[elinsd]

Never mind. I could get the fully qualified file name including the path. (I just dragged and dropped the other variable instead.) I'm now struggling with the SUBSTRING syntax over the expression so that I can just take the file name without the path.

Thanks.

 

[elinsd]

Forget about the clumsy SubString syntax. I've found a code sample for getting the file name any way you want. Tested it and it worked like a chime. I'd never figure out such a simple way to use the REVERSE function. Here is the link in case someone needs it.

http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html