Tuesday, December 11, 2012 1:32 PM
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:43 PM
Check out the first paragraph: "To loop through Excel files by using the Foreach File enumerator"
Tuesday, December 11, 2012 2:02 PM
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
- Edited by karteek_miryala Tuesday, December 11, 2012 2:13 PM
Tuesday, December 11, 2012 2:15 PM
Tuesday, December 11, 2012 2:18 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.
Wednesday, December 12, 2012 5:30 AM
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:45 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 6:17 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:
Wednesday, December 12, 2012 6:42 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:47 AM
Wednesday, December 12, 2012 1:46 PMLook into this link for solution http://msdn.microsoft.com/en-us/library/ms345182.aspx.
Venkataraman R Be Blessed by Divine
Wednesday, December 12, 2012 3:04 PMModerator
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:
Wednesday, December 12, 2012 4:00 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.