how to use parameter in For Each Loop task

Question

Hello Everyone,

I am using Script component to write Excel data to Database.

Which is working Properly but for this i have to hardcode file name in EXCEL Connection String.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Desktop\Test2.xls ;Extended Properties= Excel 8.0")

now i have multiple files in the folder i want to use for each loop component which will take my file one by one and then process that file in

Script component .

Can you tell me how to store file name in  variable in for each loop and then pass that variable in Script component so that i have not to hardcode file name.

Please provide link or soultion for it.

Answer 1

How to: Loop through Excel Files and Tables

Check out the first paragraph: "To loop through Excel files by using the Foreach File enumerator"

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 2

Hi Ajay,

declare global variable ex: SNumber int  set to 1,  to store Sheet Number, SName String  to store sheet name

in for loop set

InitExpression :@SNumber=1 ,  

depending upon no of sheets set  EvalExpression

ex:  EvalExpression : @SNumber<3 ,

then set  AssignExpression: @SNumber=@SNumber+1 

in script component :

Dts.Variables["SName"].Value = "Sheet" + Dts.Variables["SNumber"].Value + "$";

in Excel source editor set   variable name to User::SName 

and metadata should be same. for more information:http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/0746dcc0-ccba-49d7-b615-f4f89381f58c  

Answer 3

Karteek: that's a For Loop, not a For Each Loop.

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 4

Declare variable to store the filename.

Edit Foreach Loop Editor

Under Collection put folder you will be looping from

Under Variable Mappings select the variable define above.

Under Retrieve file name, choose whatever is applicable to you.

Answer 5

Hello everyone,

I just waned to know how to pass variable from for each loop to script component.

pls provide some link or solution for this problem

Answer 6

I am doing this in Script component m putting this string in Script component.

Kindly tell me how can i do it.

how to pass variable values from for each loop to script component pls provide some link and solution for it

Answer 7

There have been enough replies how you can store the current Excel filename in a variable using the For Each Loop. In the script component, you can use variables like this:

http://microsoft-ssis.blogspot.be/2011/01/how-to-use-variables-in-script.html

(I found this link after amazing amount of Googling)

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 8

I am using Script component as source.I m not getting input column.

Kindly Provide solution where i can use script component as a source and map the variables which are passed by for each loop.

Is it possible using SSIS 2005

Answer 9

I'm pretty sure there are plenty sources on the internet describing how this can be done. We're not your personal Google assistant.

---

MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

Answer 10

Look into this link for solution http://msdn.microsoft.com/en-us/library/ms345182.aspx.

---

Venkataraman R Be Blessed by Divine

Answer 11

Use the link Koen provided about getting ssis variables in a Script Component.

Alternative: You could also set the expression on the Excel Connection manager (property ExcelFilePath) and then use that connection manager in the Script Component (type source). Screenshot below creates a new flat file connection manager, but you can also select an existing connection manager.

then in code you can call that connection manager like:

Me.Connections.myExcelFile.ConnectionString

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 12

Hey its simple

1. Create FilePath variable with datatype string.

2. Click on collection make sure on top you have foreach file enumerator

3.Folder: give your excle files path like C:\user\

4.Files: *.xls and mark on fully qualified

5.go to variable mappings chose your variable.

6.Put your script task in foreach loop container.

7.Use that variable in your code so you dont have to write file names.