none
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.

    Tuesday, December 11, 2012 1:32 PM

Answers

  • 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.

    Wednesday, December 12, 2012 6:17 AM
  • 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

    Wednesday, December 12, 2012 3:04 PM

All replies

  • 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.

    Tuesday, December 11, 2012 1:43 PM
  • 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  

    Tuesday, December 11, 2012 2:02 PM
  • Karteek: that's a For Loop, not a For Each Loop.

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

    Tuesday, December 11, 2012 2:15 PM
  • 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.

    Tuesday, December 11, 2012 2:18 PM
  • 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

    Wednesday, December 12, 2012 5:30 AM
  • 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

    Wednesday, December 12, 2012 5:45 AM
  • 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.

    Wednesday, December 12, 2012 6:17 AM
  • 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

    Wednesday, December 12, 2012 6:42 AM
  • 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.

    Wednesday, December 12, 2012 6:47 AM
  • Look into this link for solution http://msdn.microsoft.com/en-us/library/ms345182.aspx.

    Venkataraman R Be Blessed by Divine

    Wednesday, December 12, 2012 1:46 PM
  • 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

    Wednesday, December 12, 2012 3:04 PM
  • 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.

    Wednesday, December 12, 2012 4:00 PM