Script task in DFT doesnt get excecuted

Question

I have a scenario where I have to get data from unknown no of excel file and their unknown no of tabs. So I created a table with 50 columns. enter image description here

Problem is ssis package is not progressing after getting into second loop.First loop gets file names and second loop gets tab names in that file. The DTF in second loop opens excel file tab and read data and write it to database table enter image description here

I created sperate variable to store file name and tab name retrived from foreach loop containers and used them in DTF task. even than DFT task is getting strucked up without any error.

Inside DFT I just have a Script source to open excel file and read data and then pass it to oledb destination. enter image description here

After tyring all weired things suppringly DFT task in loop prosessed now. Inside the foreach loop above DFT I place a script task and poped up some test. MessageBox.Show("blabla"). and its woriking. Do now how?

below is the code in script task.

public class ScriptMain : UserComponent
{
    private OleDbDataReader excelReader;
    private OleDbConnection excelConnection;
    private OleDbCommand excelCommand;
    public override void PreExecute()
    {
        base.PreExecute();
        // Open 
        GetDataFromExcelToReader(Variables.IndividualFileNamesForDFT, Variables.IndividualTabNamesForDFT);
    }
    public override void PostExecute()
    {
        base.PostExecute();
        excelReader.Close();
        excelConnection.Close();
    }
    private void GetDataFromExcelToReader(string p_strFileName, string p_strTabName)
    {
        string l_strConnectionString;
        if (File.Exists(p_strFileName))
        {
            string extension = Path.GetExtension(p_strFileName);
            extension = extension.Replace(".", "");
            if (extension.ToLower() == "xlsx")
            {
                l_strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\";";
            }
            else
            {
                l_strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 4.0;HDR=NO;IMEX=1\";";
            }
            excelConnection = new OleDbConnection(l_strConnectionString);
            excelConnection.Open();
            excelCommand = excelConnection.CreateCommand();
            excelCommand.CommandText = "SELECT * FROM [" + p_strTabName + "A1:AX1048576]";
            excelCommand.CommandType = CommandType.Text;
            excelReader = excelCommand.ExecuteReader();
        }
    }
    public override void CreateNewOutputRows()
    {
        int counter = 0;
        while (excelReader.Read())
        {
                Output0Buffer.AddRow();
                Output0Buffer.FileName = Variables.IndividualFileNamesForDFT.ToString();
                Output0Buffer.TabName = Variables.IndividualTabNamesForDFT.ToString();
                Output0Buffer.Col1 = excelReader.FieldCount > 0 ? excelReader[0].ToString() : null;
                Output0Buffer.Col2 = excelReader.FieldCount > 1 ? excelReader[1].ToString() : null;
                Output0Buffer.Col3 = excelReader.FieldCount > 2 ? excelReader[2].ToString() : null;
                Output0Buffer.Col4 = excelReader.FieldCount > 3 ? excelReader[3].ToString() : null;
                Output0Buffer.Col5 = excelReader.FieldCount > 4 ? excelReader[4].ToString() : null;
                Output0Buffer.Col6 = excelReader.FieldCount > 5 ? excelReader[5].ToString() : null;
                Output0Buffer.Col7 = excelReader.FieldCount > 6 ? excelReader[6].ToString() : null;
                Output0Buffer.Col8 = excelReader.FieldCount > 7 ? excelReader[7].ToString() : null;
                Output0Buffer.Col9 = excelReader.FieldCount > 8 ? excelReader[8].ToString() : null;
                Output0Buffer.Col10 = excelReader.FieldCount > 9 ? excelReader[9].ToString() : null;
                Output0Buffer.Col11 = excelReader.FieldCount > 10 ? excelReader[10].ToString() : null;
                Output0Buffer.Col12 = excelReader.FieldCount > 11 ? excelReader[11].ToString() : null;
                Output0Buffer.Col13 = excelReader.FieldCount > 12 ? excelReader[12].ToString() : null;
                Output0Buffer.Col14 = excelReader.FieldCount > 13 ? excelReader[13].ToString() : null;
                Output0Buffer.Col15 = excelReader.FieldCount > 14 ? excelReader[14].ToString() : null;
                Output0Buffer.Col16 = excelReader.FieldCount > 15 ? excelReader[15].ToString() : null;
                Output0Buffer.Col17 = excelReader.FieldCount > 16 ? excelReader[16].ToString() : null;
                Output0Buffer.Col18 = excelReader.FieldCount > 17 ? excelReader[17].ToString() : null;
                Output0Buffer.Col19 = excelReader.FieldCount > 18 ? excelReader[18].ToString() : null;
                Output0Buffer.Col20 = excelReader.FieldCount > 19 ? excelReader[19].ToString() : null;
                Output0Buffer.Col21 = excelReader.FieldCount > 20 ? excelReader[20].ToString() : null;
                Output0Buffer.Col22 = excelReader.FieldCount > 21 ? excelReader[21].ToString() : null;
                Output0Buffer.Col23 = excelReader.FieldCount > 22 ? excelReader[22].ToString() : null;
                Output0Buffer.Col24 = excelReader.FieldCount > 23 ? excelReader[23].ToString() : null;
                Output0Buffer.Col25 = excelReader.FieldCount > 24 ? excelReader[24].ToString() : null;
                Output0Buffer.Col26 = excelReader.FieldCount > 25 ? excelReader[25].ToString() : null;
                Output0Buffer.Col27 = excelReader.FieldCount > 26 ? excelReader[26].ToString() : null;
                Output0Buffer.Col28 = excelReader.FieldCount > 27 ? excelReader[27].ToString() : null;
                Output0Buffer.Col29 = excelReader.FieldCount > 28 ? excelReader[28].ToString() : null;
                Output0Buffer.Col30 = excelReader.FieldCount > 29 ? excelReader[29].ToString() : null;
                Output0Buffer.Col31 = excelReader.FieldCount > 30 ? excelReader[30].ToString() : null;
                Output0Buffer.Col32 = excelReader.FieldCount > 31 ? excelReader[31].ToString() : null;
                Output0Buffer.Col33 = excelReader.FieldCount > 32 ? excelReader[32].ToString() : null;
                Output0Buffer.Col34 = excelReader.FieldCount > 33 ? excelReader[33].ToString() : null;
                Output0Buffer.Col35 = excelReader.FieldCount > 34 ? excelReader[34].ToString() : null;
                Output0Buffer.Col36 = excelReader.FieldCount > 35 ? excelReader[35].ToString() : null;
                Output0Buffer.Col37 = excelReader.FieldCount > 36 ? excelReader[36].ToString() : null;
                Output0Buffer.Col38 = excelReader.FieldCount > 37 ? excelReader[37].ToString() : null;
                Output0Buffer.Col39 = excelReader.FieldCount > 38 ? excelReader[38].ToString() : null;
                Output0Buffer.Col40 = excelReader.FieldCount > 39 ? excelReader[39].ToString() : null;
                Output0Buffer.Col41 = excelReader.FieldCount > 40 ? excelReader[40].ToString() : null;
                Output0Buffer.Col42 = excelReader.FieldCount > 41 ? excelReader[41].ToString() : null;
                Output0Buffer.Col43 = excelReader.FieldCount > 42 ? excelReader[42].ToString() : null;
                Output0Buffer.Col44 = excelReader.FieldCount > 43 ? excelReader[43].ToString() : null;
                Output0Buffer.Col45 = excelReader.FieldCount > 44 ? excelReader[44].ToString() : null;
                Output0Buffer.Col46 = excelReader.FieldCount > 45 ? excelReader[45].ToString() : null;
                Output0Buffer.Col47 = excelReader.FieldCount > 46 ? excelReader[46].ToString() : null;
                Output0Buffer.Col48 = excelReader.FieldCount > 47 ? excelReader[47].ToString() : null;
                Output0Buffer.Col49 = excelReader.FieldCount > 48 ? excelReader[48].ToString() : null;
                Output0Buffer.Col50 = excelReader.FieldCount > 49 ? excelReader[49].ToString() : null;
        }
    }
}

Can you post the code for the Data Flow Script Component please as it would appear that this is where it is getting stuck — Ciarán, Dec 12 '12 at 10:51
It would help if you had a screenshot of the package. Is this is what your diagram looks like? [Foreach Loop Container (File enumerator) to grab a list of excel document file names] --> [Foreach Loop Container (Item/ADO Enumerator) to go through list and for each excel document tries to get the tab/worksheet name]. Your code runs in the second Foreach container? — booyaa, Dec 12 '12 at 11:07
@booyaa I uploaded images, but it is not shown here. Flow is here Foreach loop(loop excel file) -> script task(open excel file and get tabs) -> Foreach loop(loop tabs) -> DFT task [Script task(open excel file) -> Oledb dest (load data table)] — Arjun, Dec 12 '12 at 12:08

score 2 · Answer 1 · answered Dec 12 '12 at 11:07

2

Do you really need to read A1:AX1048576 ? Does it handle this and only read what it needs to?

Can you try to read only the parts of the excel file you need? For example if you have an excel file with 10 columns and 400 lines, try to read A1:Z9999 - wondering if the script task just gets boggled up trying to fit all this into memory, then trashing to disk since the resultset is pretty immense..

answered Dec 12 '12 at 11:07

cairnz

3,917
1
18
21

1

Yes, I'm inclined to agree with cairnz. In fact where is no need to specify the range at all. You can simply say "SELECT * FROM [" + p_strTabName + "]" – Ciarán Dec 12 '12 at 11:53
since I dont konw the no of rows I factored all teh rows. But thats not the problem. even after limiting rows to 100, it is still not processing – Arjun Dec 12 '12 at 12:05
Oh and also, just as a matter of interest. There is no need to reference Microsoft.Jet.OLEDB.4.0 as Microsoft.ACE.OLEDB.12.0 provider will be read the xls file just fine. But this is not causing you the problem. I avoid Script Components because they cannot be debugged. You can however display debug messages in message boxes to see where it is hanging. – Ciarán Dec 12 '12 at 12:06
@lynamc After tyring all weired things suppringly DFT task in foreach loop prosessed now. Inside the foreach loop above DFT I place a script task and poped up some test. MessageBox.Show("blabla"). and its woriking. – Arjun Dec 12 '12 at 12:13
That doesn't make sense and you can't leave it there. Did you change anything else? – Ciarán Dec 12 '12 at 12:26
@lynamc ya, you are right. I am trying hard to know the reason. but no luck – Arjun Dec 12 '12 at 13:04
If you are using SQL/Server then you could convert this to a Script Task and use SqlBulkCopy instead. I have had nothing but trouble with Script Components. You can at least debug Script Tasks – Ciarán Dec 12 '12 at 13:09

score 1 · Accepted Answer · answered Dec 18 '12 at 12:58

1

@Ciarán - thanks, inseted of select a specific range just opening complete tab solved my problem. "SELECT * FROM [" + p_strTabName + "]"

answered Dec 18 '12 at 12:58

Arjun

1,049
6
23
37

Script task in DFT doesnt get excecuted

2 Answers2

Linked