Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSIS Task????

Posted on 2011-05-02
12
Medium Priority
?
782 Views
Last Modified: 2012-05-11
I am looking to import a .CSV file on a daily basis.  The file layout will be identical from day to day, however, the filename will change according to the date.

Example of filename:

GenericFileName20110428.csv - Day1
GenericFileName20110429.csv - Day2
GenericFileName20110430.csv - Day3
GenericFileName20110501.csv - Day4

The filename is consistent except for the change in the date.

Is SSIS the appropriate way to set up an automated task in this instance?  I literally would like to import at a certain time every day from the same directory, looking for the specific file meeting the name match (excluding the date).

Thoughts?  Suggestions?  If this is the best way, I have set up SSIS import tasks before, but how would I accomplish it in this regard?
0
Comment
Question by:simplyfemales
  • 6
  • 5
12 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35506177
0
 

Author Comment

by:simplyfemales
ID: 35506251
From what I can see of that recommendation, it's working in the ForEach loop, which is fine, I am familiar with that process.

What I'm not sure if it includes, is information on dealing with the "wildcard" need of a filename.

I would like the task to look for the "GenericFileName" portion and ignore the date portion since it would require that to be specific everyday.  Did I miss seeing that as part of your suggestions?

Also, ultimately, on successful import, I'd like to move the file to another directory, but that's not critical right now.

Thanks for the quick response BTW.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35506268
What you need is to get all CSV files in a specific directory and import it all from SSIS package, correct?
The for each loop read each file and gets the filename and the absolute path.... and then, inside data flow you read the data from each file dinamically depending on the file being read from For Each Loop.

Did I miss something?
Regards,
Pedro
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:simplyfemales
ID: 35506314
I think I may have been a little vague on the filenames.  The files are only singular, not a group of 2 or more .csv's that are similar.  They come in one each day, but the filename will differ due to the date.

In my experience in setting up a SSIS task, it asks for an absolute filename.  My question is......  How do I set it up with a filename that would realistically include a wildcard(s)?  If the filename is GenericFilename1, the next day it would be GenericFilename2, etc.....

If the task setup the source for GenericFilename1, it would never see, 2, 3, 4 etc etc.  How do I get the task to see the future filenames?  How do I get away from the specificity and make it similar and then use a wildcard or whatever is the best process to use?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35506340
But you need the information that come in the filename?
The example I gave in the link above get data from files in a directory with different names, dinamically!
0
 

Author Comment

by:simplyfemales
ID: 35506382
Let me review it again more closely.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35506395
ok...
0
 

Author Comment

by:simplyfemales
ID: 35507574
Oops... posted on the other thread.  OK... so I found the variables.  What specifically should I be changing them to if I want to reference non-specific filenames?

On the variables, I see the uvFilename and the uvSourceFolder.  I'm not sure what the next step is at this point.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35511324
execute the package and try to understand it first, then you give me feedback, and I'll give you further details to apply to your case.
Regards,
Pedro
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35511653
Hi Please create dynamic connectionstring for .csv file using expression.

"GenericFileName" + (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) +  RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) +   ".csv"
0
 

Author Comment

by:simplyfemales
ID: 35514718
Pedro.  I have the package and am working my way through it.  I specifically would like to look for files in directory c:\Documents and Settings\Administrator\Desktop\SearchFolder

and the filename I want to find begins with MASTER_Download

The full filename is MASTER_Download_20110430.csv

I would like to take the data found in this file and import it to a specific table in MSSQL 2008 - tablename downloads_holdingpen

How do I adjust the variables or the proper places in the package to accomplish this?
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 35514756
You should change in the For Each Loop (Go to EDIT) the extension of the files, and then inside data flow replace the excel source by Flat File Source.
Send me the package and the example CS if you have some troubles!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question