Loop Through Flat Files Based On A Date Range

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

One way is to move the files to an archive directory when you're done with them. That way you only pick up the files you need to process. Just throwing that idea out there.|||Thanks for your response. One thing that I have to be prepared for is that I may have to 'reload' data. The date range for any reloading could be anything.|||Well, you could (and perhaps should?) stage the data from the flat files in a staging table. Then, once in the staging table you can keep n number of loads around, all marked by either the load date, or a unique load number. After n number of loads, you can delete the old data. Each row in the staging table would also contain the source file name.

Or, you could simply load a table with the load date and a filename. That way, you've got a table that tells you which files to grab for a given load.
Load File
1 xyz.txt
1 fhs.txt
2 jfb.txt

The above still assumes that you "archive" the files into a subdirectory or somewhere.|||The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.|||

cdun2 wrote:

The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.

Right, but the point is that your first data flow simply loads the data from the files AS IS into a table. Then in the data flow that you use today, you point them to the staging table instead of the flat file(s).|||Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.|||

cdun2 wrote:

Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.

You're going to have to add the column in the flat file source anyway, would be one argument.

Regardless, this is just one way to tackle this problem. There are other ways I'm sure.|||

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

|||

cdun2 wrote:

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

If this is the case, then the staging table example still works. In your CONTROL flow, you'd use a foreach loop to spin through the files. Each filename can be put in a variable, which can be used in a data flow inside the foreach loop. Then, using a derived column in the data flow, you'd have access to the variable that contains the current filename.|||

cdun2 wrote:

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

Cdun2,

This is an alternative approach to the issue of processing only the files that are within a start/end date range. It uses Expression and precedence constraints:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

BTW,

Just to save extra work down the road; i would recomment to import all the columns of the files from the begining.

|||Thanks again for your input. I'll dig a little deeper into this next week. As far as bringing in all of the columns of data, the attempt is to aggregate so that we can meet a reporting need, and reduce the number of rows of data that we need to store. Its a resource/performance issue for us. I can't bring in any more than I need.|||

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.|||

Phil Brammer wrote:

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.

Yes, that makes sense. Thanks again.

|||

I took a look at the example here; http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

Thank you for providing this example. I am having a difficult time making the bridge between the precendent constraint, and the Data Flow Task where the content of the qualifying flat files are inserted into the SQL Server table.

loop through each table of my DB

Is there a way to loop using a cursor in SQL-server so i can see if each columns of each tables that i loop through my DB have a specific string value and change it to something else, renaming the column if the match if correct.

any threads that i can read from or website..

thanx !!Won't you get there using:SELECT * FROM INFORMATION_SCHEMA.COLUMNS-PatP|||Table name = nMontantBilletTVQ
Table name = nMontantBilletTPS

here an example of what i try to find.

i would like to search the word 'TVQ' in my string 'nMontantBilletTVQ'

is there a string functions for that ?? just cant get one working

thanx|||Use LIKE.

-PatP

Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Looking for some general feedback on working with SQL, SSIS and SAP

This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.

There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.

So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.

How are you pulling data from SAP to SQL using DTS?

With SQL 2005, we have a preview version of the SAP .NET Data Provider for use within SSIS. You can check it out http://msdn2.microsoft.com/en-us/library/ms141761(SQL.90).aspx

SSIS will also be supported by the upcoming Biztalk R2 Adapter pack - which has adapters for SAP, Oracle & Siebel.

Go to https://connect.microsoft.com/ and look for this adapter pack - you can try out Beta2 around end-July.

|||

Currently we're not exactly pulling data. We're exporting data from SAP to a collection of flat files. Then we have a number of DTS packages that run - most nightly, one every 4 hours - and import the data into our database. Which seems damn clumsy.

Thanks for pointing me towards the .NET Data Provider, which I had a vague notion of, and to the Biztalk beta, which is something I hadn't heard of. I'll have plenty of things to research.

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.
> Jake
>

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake
"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.

> Jake
>