February 15, 2017 at 1:16 am
Hi, Am new to SSIS.
i have a requirement.
Everyday Excel file will be uploaded into one folder with naming convention as abc_15_02_2017(abc_dd_mm_yyyy) format.
I have to copy data from excel file and load it to SQL table. if file name is same everyday i can load it. But since file name is changing everyday am not able to read the file with my ETL package..
i used foreach loop to read file with different name. but getting error.
Can anyone help me with best way how to load the data from excel to SQL if excel is having different name?
Thanks in advance.
February 15, 2017 at 5:03 am
You can use variables as part of your connection manager instead. If you create a variable that has an expression value of that day's file location I.e. C:\MyFiles\abc_15_02_2017.xlsx, you can then set the ExcelFilePath propertyof your Connection Manager to have a value of that variable. Then your dataflow will also use the file that the variable equates to.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 15, 2017 at 5:42 am
Helpseeker - Wednesday, February 15, 2017 1:16 AMHi, Am new to SSIS.i have a requirement.
Everyday Excel file will be uploaded into one folder with naming convention as abc_15_02_2017(abc_dd_mm_yyyy) format.
I have to copy data from excel file and load it to SQL table. if file name is same everyday i can load it. But since file name is changing everyday am not able to read the file with my ETL package..
i used foreach loop to read file with different name. but getting error.
Can anyone help me with best way how to load the data from excel to SQL if excel is having different name?Thanks in advance.
The Foreach loop is the way to go, IMO. But if you do not post the text of your error message, how do you expect us to help? I can assure you that it works and a quick search will lead you to a site which will walk you through the process.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 15, 2017 at 6:34 am
Phil Parkin - Wednesday, February 15, 2017 5:42 AMThe Foreach loop is the way to go, IMO. But if you do not post the text of your error message, how do you expect us to help? I can assure you that it works and a quick search will lead you to a site which will walk you through the process.
Would it be? If those files aren't archived, after a little while that's going to be a lot of files to loop through that have already been loaded. if the task is running daily, and will only need to look at that day's file, why bother looping through all the other files?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 15, 2017 at 6:45 am
Thom A - Wednesday, February 15, 2017 6:34 AMPhil Parkin - Wednesday, February 15, 2017 5:42 AMThe Foreach loop is the way to go, IMO. But if you do not post the text of your error message, how do you expect us to help? I can assure you that it works and a quick search will lead you to a site which will walk you through the process.Would it be? If those files aren't archived, after a little while that's going to be a lot of files to loop through that have already been loaded. if the task is running daily, and will only need to look at that day's file, why bother looping through all the other files?
Every file-import process I've ever dealt with archives the source files after processing them, so I'll admit that I took that as read.
If you don't do this, you could easily end up with a mass of processed and unprocessed files.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 15, 2017 at 6:53 am
Phil Parkin - Wednesday, February 15, 2017 6:45 AMThom A - Wednesday, February 15, 2017 6:34 AMPhil Parkin - Wednesday, February 15, 2017 5:42 AMThe Foreach loop is the way to go, IMO. But if you do not post the text of your error message, how do you expect us to help? I can assure you that it works and a quick search will lead you to a site which will walk you through the process.Would it be? If those files aren't archived, after a little while that's going to be a lot of files to loop through that have already been loaded. if the task is running daily, and will only need to look at that day's file, why bother looping through all the other files?
Every file-import process I've ever dealt with archives the source files after processing them, so I'll admit that I took that as read.
If you don't do this, you could easily end up with a mass of processed and unprocessed files.
Agreed. I didn't want to, however, assume that archiving was happening. Are your files being archived after processing, Helpseeker? Also, as Phil stated, we need your error message.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply