Solved

loop for import excel file with date prefix using ssis package

Posted on 2015-02-18
7
106 Views
Last Modified: 2016-02-15
Hi,

I have a requirement to load data into SQL table. The excel files come with the date on it as below format:

xxxxxxx_2015_02_19_050021

Please let me know how do I loop through the files and load the data into a sql table. and also store this files in SQL table


thanks in advance
0
Comment
Question by:ken hanse
  • 3
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 100 total points
ID: 40617878
In general, this would be an SSIS package with a For Each File loop to sweep through a given folder for files of a certain type, then those files can be used as the data source in a data flow task.

So .. are these files exactly the same schema?

Also, be advised that SSIS and Excel have known compatibility issues, and Excel docs are not good for data sources as users can do thousands of different things to throw off both normalization and the source to target mapping.
0
 

Author Comment

by:ken hanse
ID: 40627889
Yes, it is same schema. And using 32bit excel driver.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40630731
How many files will you process by day?
All files? Only the day file?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ken hanse
ID: 40638760
Only the day file
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40639173
Then use the Import Data wizard to create a daily job and assure that the file has always the same name.
0
 

Author Comment

by:ken hanse
ID: 40640645
sorry Vitor, I just need to explained bit more.

the file format is:

xxxxxx_yyyy_mm_dd_mmss

one file per day

This is on excel xls format. I need dynamically read the file,


Thanks
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 400 total points
ID: 40647302
Then use Jim Horn's suggestion. Create a package in SSIS than can go through all files and process only the files that you want.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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