HI All, I have excel files which have more work sheets. Point 1 : The excel sheet names will changes daily 2 : The excel sheet order will vary 3 : We have to load the particular name worksheet in particular table only. Need help on this. two days time given. Can anyone help with the concepts and script task code if any. I don't know to code script task only SSIS i known.

Loop over Excel tables: http://msdn.microsoft.com/en-us/library/ms345182(v=sql.90).aspx In the For Each Loop, you create a dataflow for every possible sheet. Before you run one of those dataflows, you need to decide which dataflow to run. I assume you can deduce this from the sheetname?MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Koen, I did the looping of excel files and to read from all sheets of excel, i want to know how to restrict the table load with the work sheet names that are changing. suppose if i have work sheet name as sheet55$, the sheet can be any position in a excel file, we need to get exact sheet and load in particular table only. similarly we have to do for many sheets to load many tables.

Not sure what you're getting at. As I said, inside the ForEach Loop you need to check which sheet is the current one and which corresponding dataflow you need to run.MCTS, MCITP - Please mark posts as answered where appropriate.

this link will help you in getting the excel files what ever the names and excel sheet names what ever the sheet name. But you mentioned ......"We have to load the particular name worksheet in particular table only" ...... your answer is 1- if its a fix name and wont change , use the link and set the SHEET TAB variable to the sheet name and set the MUTLI SHEET = FALSE 2- if the sheet names changes but the COLUMN names doesn't (the column names can move arround, but not change) you will have to add more to the link to capture the column and check if the exist if not go to next sheet something like If Dts.Variables("uVar_ColumnFieldSourceFlagCheckColumn").Value = True Then ''------------------------------------------------------------------------------------- Dim columnsInTable As DataTable Dim columnInTable As DataRow Dim columnRestrictions(3) As String '= (Nullable, Nullable, CurrentSheet , nullable ) columnRestrictions(2) = CurrentSheet ' this will restrict the columns list to the current sheet only Dim strCurrentColumnFieldSourceFieldNames As String = "" columnsInTable = oledbExcelConnection.GetSchema("COLUMNS", columnRestrictions) Dim i As Integer = columnsInTable.Rows.Count Dim strTemp As String = "" For Each columnInTable In columnsInTable.Rows 'If columnInTable.Item("TABLE_NAME").ToString = CurrentSheet Then strTemp = columnInTable.Item("COLUMN_NAME").ToString & "," If InStr(strColumnFieldSourceFieldNames, strTemp) Then 'strCurrentColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames & strTemp strCurrentColumnFieldSourceFieldNames &= strTemp 'MsgBox(currentColumn) End If Next strColumnFieldSourceFieldNames = SortColumnFieldsInAString(strColumnFieldSourceFieldNames) strCurrentColumnFieldSourceFieldNames = SortColumnFieldsInAString(strCurrentColumnFieldSourceFieldNames) Dim j As Integer = 0 If strColumnFieldSourceFieldNames = strCurrentColumnFieldSourceFieldNames Then MsgBox("yes") 'j = 1 Else MsgBox("No") 'j = 0 End If ''------------------------------------------------------------------------------------- End If and LoopForNumberOfRealTables += 1 ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1) ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet 'MsgBox(CurrentSheet & " --- " & strConnectionString) End If Next oledbExcelConnection.Close() Else ReDim Preserve ListOfExcelSheets(0) ListOfExcelSheets(0) = Dts.Variables("uVar_SourceActiveSheetName").Value.ToString End If Dts.Variables("uVar_ArrayOfExcelSheetNames").Value = ListOfExcelSheets Dts.TaskResult = ScriptResults.Success and Public Function SortColumnFieldsInAString(ByVal strColumnFieldNames As String) As String 'Dim strColumnFieldList As String() Dim strColumnFieldList As New List(Of String) Dim strCurColumnField As String Dim FirstPointer As Integer = 0 Dim NextCommaPosition As Integer = 0 Dim strLength As Integer = 0 Dim iNumberOfLoops As Integer = CountCharacter(strColumnFieldNames, ",") For iLoop = 1 To iNumberOfLoops NextCommaPosition = strColumnFieldNames.IndexOf(",", FirstPointer) strLength = NextCommaPosition - FirstPointer strCurColumnField = strColumnFieldNames.Substring(FirstPointer, strLength) strColumnFieldList.Add(strCurColumnField) FirstPointer = FirstPointer + strLength + 1 ' Move pointer forward Next strColumnFieldList.Sort() strColumnFieldNames = "" For iLoop = 1 To iNumberOfLoops strColumnFieldNames = strColumnFieldNames & strColumnFieldList(iLoop - 1) strColumnFieldNames = strColumnFieldNames & "," Next Return strColumnFieldNames End Function Public Function CountCharacter(ByVal value As String, ByVal ch As Char) As Integer Dim cnt As Integer = 0 For Each c As Char In value If c = ch Then cnt += 1 Next Return cnt End Function End Class Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Koen, I did the looping of excel files and to read from all sheets of excel, i want to know how to restrict the table load with the work sheet names that are changing. suppose if i have work sheet name as sheet55$, the sheet can be any position in a excel file, we need to get exact sheet and load in particular table only. similarly we have to do for many sheets to load many tables. I dont think you will be able to find the sheet location because the list is provided in a sort order, and we cant control that ExcelDataTable = oledbExcelConnection.GetSchema("Tables") SheetCount = ExcelDataTable.Rows.Count For Each ExcelSheet In ExcelDataTable.Rows ' Maybe we can use --->>>>> ????? ExcelDataTable.Rows(0).Item (2) CurrentSheet = ExcelSheet.Item("TABLE_NAME").ToString CurrentSheet = CurrentSheet.Replace("'", "") If Right(CurrentSheet, 1) = "$" Then I am assuming that you want to load each sheet to a specific table, if so you will need a SCRIPT TASK and SQL table that . 1- SQL table has the columns of the destination table that can be maped to the excel column 2- Select all excel column for each sheet one by one and see if ALL the column names exist in which record of the sql table to be able to find the right destination table 3- then you have to ????? redirect(maybe copy) the file to another folder with the right sheet name (must be passed as a parameter. what ever you have to set/map the right sheet to the right destination table. Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).