Hi, I have a excel file coming into SSIS source. Every time file structure gets change and I want to make it automated so I want to know how many input fields are coming into excel file so according to that I can create dynamic tables with columns varies. I have did same thing with csv file by using script task in which I was counting commas and according to that I am creating dynamic table. Please help. Thanks in advance!!Vicky

What ever you do basically you would be able to use a DFT, unless you make your DFT programmatically bottom line you can capture the Field(column) names of an excel file within SSIS by using th ementioned code in Script task 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 YOU WILL NEED TO CHANGE THE CODE Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

I have did same thing with csv file by using script task in which I was counting commas and according to that I am creating dynamic table. for CSV you will only need to read ONLY the first line of the file that has the COLUMN names by using Dim objTextFileReader As New StreamReader(strPathFileName) Dim sFirstLineOnlyLine As String = "" sFirstLineOnlyLine = objTextFileReader.ReadLine() '''' Read only one line (the first line that has the columns) 'If Not sFirstLineOnlyLine Is Nothing Then If sFirstLineOnlyLine = strSourceColumnList T Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).