在SSIS脚本转换中循环访问数据库 [英] Loop through database in SSIS script transformation
问题描述
我正在尝试使用SSIS脚本任务将值从表传输到文本文件。文本文件将用于打印检查，因此它的格式如此。我有临时检查表流向脚本转换组件。包成功完成，但只在文本文件中捕获数据库的最后一行。我研究了如何遍历每一行，我似乎越来越困惑。我不确定是否必须在包中配置输入/输出或创建变量以在脚本中调用。我对C＃很新，所以这也没有帮助。任何建议/指导将不胜感激。下面是我目前获得所需格式的代码。我需要遍历数据库并应用每行/支票的格式打印。
public 覆盖 void Input0_ProcessInputRow（Input0Buffer Row）
{
StreamWriter sw = new StreamWriter（ C：\\test.txt）;
sw.WriteLine（ XXXXXXXXXXXX + XXXX .PadLeft（ 52 ）+ Row.CheckID
+ < span class =code-string> \ r + \ nXXXXXXXXXXXXXX + XXXXXX \ r .PadLeft（ 27 ）
+ \ nP.O. BOX XXXX + 24小时银行 .PadLeft（ 36 ）
+ XX-XXXX .PadLeft（ 27 ）+ \\ r
+ \ nXXXXXXXX，XX XXXXX + ， .PadLeft（ 17 ）+ < span class =code-string> ------- \r .PadLeft（ 42 ）
+ \ n + XXXX \r .PadLeft（ 71 ）
+ \ n .PadRight（ 50 ）+ Row.FName + + Row.LName + .PadLeft（ 19 ）+ DateTime.Now .ToString（ MMM dd yyyy）。ToUpper（）
+ < span class =code-string> \ rr + \ n
+ \ rr + \ n + \\ \\ r
+ \ n .PadRight（ 8 ）+ ** XXX DOLLARS AND 00/100 ** + .PadLeft（ 37 ）+ Row.Amount
+ \ rr + \ n
+ \ rr + \ n + \ rr
+ \\ \\ n .PadRight（ 11 ）+ Row.VendorName1
+ \ r + \ n .PadRight（ 11 ）+ Row.VendorName2
+ \ r + \ n .PadRight（ 11 ）+ Row.VendorName3
+ \r + \ n .PadRight（ 11 ）+ Row.VendorAddress1
+ \ rr + \ n .PadRight（ 11 ） + Row.VendorAddress2
+ \ rr + \ n .PadRight（ 11 ）+ Row.VendorCity + < span class =code-string> ， + + Row.VendorState + .PadLeft（ 4 ）+ + Row.VendorZip
+ \ r + \ n + \ rr
+ \ n .PadRight（ 11 ）+ XXXX + Row.CheckID + + XXXXXX + + XXXXXXXXXX
+ \ r + \ n + \ r
+ \ rr + \ n + \ rr
+ \ rr
+ \ n + XXXX - .PadLeft（ 71 ）+ Row.CheckID
+ \ rr + \ n + \ rr
+ \ r
+ \ n + DateTime.Now.ToString（ MM / dd / yyyy ）+ .PadLeft（ 2 ）+ Row.ClientID + .PadLeft（ 3 ）+ .PadLeft（ 3 ）+ Row.FName +
+ Row.LName + .PadLeft（ 9 ）+ Row.TCDesc1 + .PadLeft（ 29 ）+ Row.Amount
+ \ rr + \ n
+ .PadLeft（ 39 ）+ TCDesc2 + Row.TCDesc2
+ \ rr + < span class =code-string> \ n
+ .PadLeft（ 39 ）+ TCDesc3 + Row.TCDesc3
+ \ r + \ n + \ rr
+ \ r + \ n + \ rr
+ \ rr + \ n + \ r
+ \ rr + \ n + \ rr
+ \ rr + \ n + \ rr
+ \ rr + \ n + < span class =code-string> \ rr
+ \ r + \ n + \ rr
+ \ rr + \ n + \ rr
+ < span class =code-string> \ rr
+ \ rr
+ \r
+ \ rr
+ \ rr
+ \ n + .PadLeft（ 81 ）+ Row.ClientID + .PadLeft（ 9 ）+ Row.FName + + Row。 LName的;
sw.Close（）;
}
我的尝试：
我尝试使用执行SQL任务和ForEach循环容器，它一直出错。无法回想起特定的错误。
只有数据库的最后一行写入文件的原因是因为每次调用时都要重新创建文件这个Input0_ProcessInputRow方法。你需要创建并关闭循环以外的文件
做这样的事情：
< pre lang =C＃> public void Input0_ProcessFile（）
{
< span class =code-comment> //
// < span class =code-comment>无论你做什么来从数据库获取数据输出
//
// 创建文件一次！
使用（ var sw = new StreamWriter（ c：\\ temp \\ test.txt ））
{
// 处理datainput中的每一行
foreach （ var row in datainput）
Input0_ProcessInputRow（sw，row）;
}
}
注意我也把文件放到子文件夹中。将文件存储在根文件夹C：\上是不好的做法，在许多情况下，您将无法这样做，特别是如果此脚本在服务器上运行。这可能是您遇到的错误。
另请注意，我已更改方法的签名以传递对StreamWriter的引用public 覆盖 void Input0_ProcessInputRow（StreamWriter） sw，Input0Buffer Row）
{
...这是MSDN参考（侧栏中链接中的页面有更多链接）。 如何：将文本写入文件 [ ^ ]
另外考虑使用System.Environment.NewLine而不是所有这些转义字符。
另一种方法是将数据转换为XML文件，并使用HTML Template / XSLT将数据转换为报告 - 请参阅创建和填充HTML模板（Windows CE .NET 4.2） [ ^ ]
I am trying to use an SSIS script task to transfer values from a table to a text file. The text file will be used to print checks, so it is formatted as such. I have Temporary Check table flowing to a Script Transformation component. The package completes successfully but only the last row from the database is captured in the text file. I have researched how to iterate through each row and I seem to be getting more confused. I am not sure if I have to configure the input/output in package or create variables to call out in the script. I am very new to C# so that is not helping either. Any advice/guidance would be greatly appreciated. Below is the code I have currently to achieve the required format. I need to loop through the database and apply the formatting for each row/ check to be printed.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
StreamWriter sw = new StreamWriter("c:\\test.txt");
sw.WriteLine("XXXXXXXXXXXX" + "XXXX".PadLeft(52) + Row.CheckID
+ "\r" + "\nXXXXXXXXXXXXXX" + "XXXXXX\r".PadLeft(27)
+ "\nP. O. BOX XXXX" + "24 HOUR BANKING".PadLeft(36)
+ "XX-XXXX".PadLeft(27) + "\r"
+ "\nXXXXXXXX, XX XXXXX" + ",".PadLeft(17) + "-------\r".PadLeft(42)
+ "\n " + "XXXX\r".PadLeft(71)
+ "\n ".PadRight(50) + Row.FName + " " + Row.LName + " ".PadLeft(19) + DateTime.Now.ToString("MMM dd yyyy").ToUpper()
+ "\r" + "\n "
+ "\r" + "\n " + " \r"
+ "\n ".PadRight(8) +"**XXX DOLLARS AND 00/100**" +" ".PadLeft(37)+Row.Amount
+ "\r" + "\n "
+ "\r" + "\n " + " \r"
+ "\n ".PadRight(11)+Row.VendorName1
+ "\r" + "\n ".PadRight(11)+Row.VendorName2
+ "\r" + "\n ".PadRight(11)+Row.VendorName3
+ "\r" + "\n ".PadRight(11)+Row.VendorAddress1
+ "\r" + "\n ".PadRight(11)+Row.VendorAddress2
+ "\r" + "\n ".PadRight(11)+Row.VendorCity+","+ " "+Row.VendorState+" ".PadLeft(4)+" "+Row.VendorZip
+ "\r" + "\n " + " \r"
+ "\n ".PadRight(11)+"XXXX"+Row.CheckID + " "+"XXXXXX"+" "+"XXXXXXXXXX"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r"
+ "\n " +"XXXX-".PadLeft(71) + Row.CheckID
+ "\r" + "\n " + " \r"
+ "\r"
+ "\n" + DateTime.Now.ToString("MM/dd/yyyy") + " ".PadLeft(2) + Row.ClientID + " ".PadLeft(3) + " ".PadLeft(3) + Row.FName + " "
+ Row.LName + " ".PadLeft(9) + Row.TCDesc1 + " ".PadLeft(29) + Row.Amount
+ "\r" + "\n "
+ " ".PadLeft(39) + "TCDesc2" + Row.TCDesc2
+ "\r" + "\n "
+ " ".PadLeft(39) + "TCDesc3" + Row.TCDesc3
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r" + "\n " + " \r"
+ "\r"
+ "\r"
+ "\r"
+ "\r"
+ "\r"
+ "\n" + " ".PadLeft(81) + Row.ClientID + " ".PadLeft(9) + Row.FName + " " + Row.LName;
sw.Close();
}
What I have tried:
I tried using an Execute SQL task and a ForEach Loop Container and it kept erroring out. Cannot recall the specific error.
The reason that only the last row from the database is written to the file, is because you are recreating the file each time you call thisInput0_ProcessInputRowmethod. You need to create and close the file outside of the loop
Do something like this instead:
public void Input0_ProcessFile() { // // Put in here whatever you do to get datainput from the database // //Create the file once! using (var sw = new StreamWriter("c:\\temp\\test.txt")) { //Process each row in the datainput foreach (var row in datainput) Input0_ProcessInputRow(sw, row); } }Note I've also put the file into a sub-folder. It is bad practice to store files on the root folder C:\ and in many cases you will be prevented from doing so, particularly if this script is running on the server. This may be the error you encountered.
Also note that I've changed the signature of your method to pass in the reference to the StreamWriterpublic override void Input0_ProcessInputRow(StreamWriter sw, Input0Buffer Row) { ...Here is the MSDN Reference (there are more links in the sidebar to the page in the link). How to: Write Text to a File[^]
[Edit] As an aside consider usingSystem.Environment.NewLineinstead of all of those escape characters.
Another alternative would be to convert the data into and XML file and use an HTML Template/XSLT to transform the data into a "report" - see Creating and Populating an HTML Template (Windows CE .NET 4.2)[^]
这篇关于在SSIS脚本转换中循环访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
