DFT 中的脚本任务没有被执行 [英] Script task in DFT doesnt get excecuted
问题描述
我有一个场景，我必须从未知的 excel 文件和未知的选项卡中获取数据.所以我创建了一个包含 50 列的表.
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.
问题是进入第二个循环后 ssis 包没有进展.第一个循环获取文件名，第二个循环获取该文件中的选项卡名称.第二个循环中的 DTF 打开 excel 文件选项卡并读取数据并将其写入数据库表
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
我创建了 sperate 变量来存储从 foreach 循环容器中检索到的文件名和选项卡名称，并在 DTF 任务中使用它们.甚至 DFT 任务都没有出现任何错误.
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.
在 DFT 中，我只有一个脚本源来打开 excel 文件并读取数据，然后将其传递给 oledb 目标.
Inside DFT I just have a Script source to open excel file and read data and then pass it to oledb destination.
在完成所有繁琐的工作后，现在循环处理 DFT 任务.在 DFT 上方的 foreach 循环中，我放置了一个脚本任务并弹出了一些测试.MessageBox.Show("blabla").及其工作原理.现在怎么做?
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;
}
}
}
推荐答案
@Ciarán - 谢谢，插入选择特定范围只需打开完整选项卡即可解决我的问题."SELECT * FROM [" + p_strTabName + "]"
@Ciarán - thanks, inseted of select a specific range just opening complete tab solved my problem. "SELECT * FROM [" + p_strTabName + "]"
这篇关于DFT 中的脚本任务没有被执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
