Use script task to dynamically get Excel file as source file connection

Question

Use script task to dynamically get Excel file as source file connection

Viewing 15 posts - 16 through 30 (of 53 total)

You must be logged in to reply to this topic. Login to reply

Jenny Zhou-459989 SSCommitted Points: 1961 More actions · Answer 1

Hi ayemya, can you describe the steps you did. I tried but still no luck.

I would get error running the foreach loop container if I put the dummy file path as the value of the variable @filename and use this variable as an expression for ExcelFilePath or ConnectionString property for the Excel Connection Manager.

Thanks!!

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

Zenny,

Firstly, apologies for not replying earlier...

There has to be some logical explanation for why your For Each Loop Container is not working. Just a quick question, are you putting the Full Filename (in other words, the file name, file extension as well as directory) as the excel file path?

If its not sensitive, why not post the .dtsx?

Phil

ayemya Hall of Fame Points: 3495 More actions · Answer 3

Jenny,

I followed the tutorial of this link. It works for me.

http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

Jenny Zhou-459989 SSCommitted Points: 1961 More actions · Answer 4

Phil, yes I put the whole thing: directory, filename and extension (.xls) in the excel file path. I'll look at the link which worked for ayemya, and if it still doesn't work out, I'll post the .dtsx here.

Thank you both!!

GSquared SSC Guru Points: 260824 More actions · Answer 5

I recently set something up to import Excel files from a directory, with different names for the files. In order to get it to work, I had to build the connection string dynamically, with the Jet data then a dynamic file name, then the sheet name.

It works (though I do have another problem with it, in that a column with time values in it imports the ones that are formatted as text, and takes the ones that are formatted as time (floating point numbers) and imports them as Null; still working on a solution for that one). (Edit: Found the cause and solution for this, so now the whole package works as needed.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jnp273 Valued Member Points: 69 More actions · Answer 6

Hey I have similar kind of situation. will really appreciate help.

I have to take zip files one by one from a folder and then unzip them and save them at a location. I tried to use For each loop container and Execute process task but the Execute process task didnot update to the next file. It just take the first one and repeats the same operation for the number of files availablle in same folder?

Any Idea how can I make it work ?

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 7

Just a guess, you may have neglected to set the expression in your input data set connection to pick up the file name from a variable.

jnp273 Valued Member Points: 69 More actions · Answer 8

Can you explain me how can I set an expression with the varibale? and also if the coding is required then can you explain me with some sample coding?

Thanks

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 9

In my case (looping through Excel files) it is hidden away under Excel Connection Manager\Properties\Expressions (They start out hidden, you need to click the + to see them.) In my case the parameter variable was "ExcelFilePath" and was set to "@[User::FilePath] +"\\"+@[User::FileName]".

My guess is that for a flat file connection manager you need an expression that sets "Connection String" to the full path of your input file, formed using the file name variable you set in the For each Loop container..

All this assumes that you have created a variable, and set your for each loop container (enumerator=Foreach File Enumerator) collection to Retrieve File Name in the format you wish.

Good luck!

jnp273 Valued Member Points: 69 More actions · Answer 10

I think you misunderstood me. I have to unzip the all the zip files so I will have to use Execute process task. so, can you do the same with "Execute Process Task" with the variable so it will take all the zip files one by one and execute them?"

Please Reply ASAP.............

Thanks..

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

I think not. But in a script task you could probably define a read-write variable that get's set to whatever your input flat file source needs. The example syntax I have is:

Dts.Variables("TabName").Value = excelTables(0)

where "TabName" was the name of my output variable, and whatever value you need to set is on the right of the = sign. (Mine was from an array, dont let that lead you astray.)

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 12

Okey,

I decided to write a sample .dtsx and post it here, complete with 3 sourcefiles, which I simply want to import to an OLEDB destination and then to move the file to archive.

This is what I did:

a) Created a folder on my c drive called SSISForeachLoopDemo

b) Created a package called ForeachExcelFileDemo. The value I inserted was as following: "C:\SSISForeachLoopDemo\File1.xls"

c) Created a package variable called : varFileName

d) Created a Excel Connection Manager pointing to my first file. Dont worry about the hardcoding here, you simply want to pick up the metadata on the file. In the expressions, I added one which I pointed to the ExcelFilePath.

e) Created a OLEDB Connection Manager pointing to my localhost Adventureworks.

f) Created a preparation SQL task which simply checks for table existence and creates the demo table. Note the use of NVARCHAR, because of unicode conversion in Excel (truly annoying but so be it...)

g) Dragged a For Each Excel File Found enumerator onto the control flow, and changed as following:

- Changed DelayValidation to true (call me pedantic but disciplined enough to always do this)

- Changed the folder to "C:\SSISForeachLoopDemo\"

- Changed the files to "*.xls"

- Changed the variable mappings to "User::varFileName"

h) Dragged a Dataflow Task into the For Each Enumerator, and same like before, delayed the package validation

i) In the Dataflow Task, I added a Excel Source and OLEDB destination. I then changed the metadata to read from the excel file. I dragged from source to destination and did the necessary metadata field mappings. If you ran the package round about now and viewed the data in the table, you would notice that the loop is already working and changing the connection strings.

j) I created a subfolder called Archive within "C:\SSISForeachLoopDemo\"

k) I created a variable called "varArchiveFolderName" and set the value equal to "C:\SSISForeachLoopDemo\Archive\"

l) I dragged a file system task inside the loop, and added a success step from the dataflow task. I altered as following:

- Changed the Operation to Move File

- Changed the Source connection (set the IsSourcePathVariable equal to true, set the source connection equal to varFileName)

- Changed the Destination connection (set the IsDestinationVariable equal to true, set the Destination connection equal to varArchiveFolderName

TADA

I have posted the 3 excel files plus the .dtsx, but really this is about as simple as SSIS will ever get.

On the subject of Zipping, you will have to write some code to extrapolate the filname without foldername. I wrote something which calls the Winrar library inside the for each file enumerator and it works like a charm

Good Luck

~PD

Jenny Zhou-459989 SSCommitted Points: 1961 More actions · Answer 13

Phil, I followed exactly the steps in the link provided by ayemya and it worked up until step 18, after the foreach loop step is set up, and once I changed the file in the folder to be a different name (but same columns) and there is only one file it started to err. I noticed that if I put two excel files in the folder then it worked with no error. So i think maybe the foreach loop only works for Excel files when there are multiple files in the same folder?

I have attached the package and the sample excel. If you or someone could figure out what's wrong with it I'd really appreciate it!!

ayemya Hall of Fame Points: 3495 More actions · Answer 14

So i think maybe the foreach loop only works for Excel files when there are multiple files in the same folder?

NOT true.

For my case, I have to do Data conversion between excel source and datasource destination. i have a dummy file and one or multiple files in the folder. It works perfect.

Does all ur files has same header row?

Jenny Zhou-459989 SSCommitted Points: 1961 More actions · Answer 15

yes they have the same header since there is only one file. I changed the filename for testing, because in reality we only have one file in that folder each week and the file name is different but the structure/format/header is the same.