InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:52:48 GMT20http://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspx[quote][b]sam 55243 (1/2/2013)[/b][hr]Hi Phil,I had to make few changes in my script component code, so as to generate only one consolidated output file. :-)If you had seen my code earlier i had created both filestream & streamwriter object inside the sub module 'Input0_ProcessInputRow', now i have added two new submodule one is 'Pre execute and another is 'PostExecute'. Inside PreExecute i tried to create new object for filestream & streamwriter and inside postexecute sub module i tried to dispose the objects i have created.here is my updated code in script component:[code="vb"]Option Explicit OffOption Strict OffImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports System.IOImports System.TextPublic Class ScriptMain Inherits UserComponent Dim strtarget As String Dim FileFormat As String Dim fPHTaxReport As FileStream Dim swPHTaxWriter As StreamWriter Dim UTF8Encoding As Encoding Public Overrides Sub PreExecute() FileFormat = Me.Variables.Varfileformat.ToString() strtarget = Me.Variables.VarTargetFilepath.ToString + "Consolidated Report to PH Tax Department_" + DateTime.Now.ToString("yyyyMMddhhmm") + FileFormat fPHTaxReport = New FileStream(strtarget, FileMode.Append, FileAccess.Write) swPHTaxWriter = New StreamWriter(fPHTaxReport, UTF8Encoding.UTF8) MyBase.PreExecute() End Sub Public Overrides Sub PostExecute() swPHTaxWriter.Flush() fPHTaxReport.Close() MyBase.PostExecute() End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Row.RecordType = "H" Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersTradeName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth + "|" + _ Row.FiscalYearEnding) ElseIf (Row.RecordType = "D") Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth) End If End SubEnd Class[/code]One quick observation i found:if i run the old code for record count less than 800 than i'm getting 1 o/p file, if its more than 1000 then i'm getting multiple output file.But with the above updated code there don't seems to be any issue. Withn 'N' no. of records coming frm the source i'm getting only one o/p file created in the shared path.Not sure what exactly is ithe issue with script component....Is it something to do with no of records cached in the buffers???Any ideaThanksSam[/quote]Hi SamI think you've cracked it. Your solution now is much nicer. Previously everything was running once for every row of data and, in particular, this expression was being recalculated every time:DateTime.Now.ToString("yyyyMMddhhmm") What this means, I think, is that if your process spanned more than a minute you would get multiple output files.Wed, 02 Jan 2013 05:52:16 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxHi Phil,I had to make few changes in my script component code, so as to generate only one consolidated output file. :-)If you had seen my code earlier i had created both filestream & streamwriter object inside the sub module 'Input0_ProcessInputRow', now i have added two new submodule one is 'Pre execute and another is 'PostExecute'. Inside PreExecute i tried to create new object for filestream & streamwriter and inside postexecute sub module i tried to dispose the objects i have created.here is my updated code in script component:[code="vb"]Option Explicit OffOption Strict OffImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports System.IOImports System.TextPublic Class ScriptMain Inherits UserComponent Dim strtarget As String Dim FileFormat As String Dim fPHTaxReport As FileStream Dim swPHTaxWriter As StreamWriter Dim UTF8Encoding As Encoding Public Overrides Sub PreExecute() FileFormat = Me.Variables.Varfileformat.ToString() strtarget = Me.Variables.VarTargetFilepath.ToString + "Consolidated Report to PH Tax Department_" + DateTime.Now.ToString("yyyyMMddhhmm") + FileFormat fPHTaxReport = New FileStream(strtarget, FileMode.Append, FileAccess.Write) swPHTaxWriter = New StreamWriter(fPHTaxReport, UTF8Encoding.UTF8) MyBase.PreExecute() End Sub Public Overrides Sub PostExecute() swPHTaxWriter.Flush() fPHTaxReport.Close() MyBase.PostExecute() End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Row.RecordType = "H" Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersTradeName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth + "|" + _ Row.FiscalYearEnding) ElseIf (Row.RecordType = "D") Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth) End If End SubEnd Class[/code]One quick observation i found:if i run the old code for record count less than 800 than i'm getting 1 o/p file, if its more than 1000 then i'm getting multiple output file.But with the above updated code there don't seems to be any issue. Withn 'N' no. of records coming frm the source i'm getting only one o/p file created in the shared path.Not sure what exactly is ithe issue with script component....Is it something to do with no of records cached in the buffers???Any ideaThanksSamWed, 02 Jan 2013 04:58:06 GMTsam 55243http://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxIs there any additional complexity to the SSIS package which you have not mentioned?Is the data flow in a Foreach container, perhaps?No idea why replacing a proc with another version which is exactly the same should cause this behaviour - sounds very unlikely.Tue, 01 Jan 2013 08:04:36 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxHi Phil,[quote]Can you tell me the file names of the two files which are generated please? Are they created at the same time?[/quote]The files that are generated are of different time stamp, they differ by few minutes/sec apart.ThanksSamTue, 01 Jan 2013 07:57:45 GMTsam 55243http://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxI'm puzzled by this. The Script Component only seems to write to a single file.Can you tell me the file names of the two files which are generated please? Are they created at the same time?Tue, 01 Jan 2013 07:49:21 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxHi Phil,Please find the script component code below....[code="vb"]Option Explicit OffOption Strict OffImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Pipeline.WrapperImports Microsoft.SqlServer.Dts.Runtime.WrapperImports System.IOImports System.Text<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _<CLSCompliant(False)> _Public Class ScriptMain Inherits UserComponent Dim fPHTaxReport As FileStream Dim swPHTaxWriter As StreamWriter Dim UTF8Encoding As Encoding Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim FileFormat As String = Me.Variables.varfileformat.ToString() Try Dim strtarget As String = Me.Variables.varTargetFilepath.ToString + "Consolidated Report to PH Tax Department_" + DateTime.Now.ToString("yyyyMMddhhmm") + FileFormat fPHTaxReport = New FileStream(strtarget, FileMode.Append, FileAccess.Write) swPHTaxWriter = New StreamWriter(fPHTaxReport, UTF8Encoding.UTF8) If Row.RecordType = "H" Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersTradeName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth + "|" + _ Row.FiscalYearEnding) ElseIf (Row.RecordType = "D") Then swPHTaxWriter.WriteLine(Row.RecordType + "|" + _ Row.TransactionType + "|" + _ Row.TINNumber + "|" + _ Row.OwnersRegisteredName + "|" + _ Row.OwnersLastName + "|" + _ Row.OwnersFirstName + "|" + _ Row.OwnersMiddleName + "|" + _ Row.OwnersAddress1 + "|" + _ Row.OwnersAddress2 + "|" + _ CStr(Row.ExemptSales) + "|" + _ CStr(Row.ZeroRatedSales) + "|" + _ CStr(Row.TaxableSales) + "|" + _ CStr(Row.OutputTax) + "|" + _ Row.RDOCode + "|" + _ Row.TaxableMonth) End If swPHTaxWriter.Flush() fPHTaxReport.Close() Catch ex As Exception swPHTaxWriter.Flush() fPHTaxReport.Close() Throw ex End Try End SubEnd Class[/code]ThanksTue, 01 Jan 2013 06:52:54 GMTsam 55243http://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxCan you share the code inside the Script Component?Tue, 01 Jan 2013 06:47:36 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1401592-148-1.aspxHi all,Need help in solving an issue.i have a SSIS pkg deployed in INT & Dev env respectively. The data flow is like thisOne oledb source and script component component (using it as Destination)Inside oledb source component i'm using an stored procedure as Sql Command. Please see the attached screenshot of my DataFlow.Now the issue is:If i execute the pkg in Dev env i'm getting one output file generated in the shared path, however if i execute the same pkg in INT env i'm getting two o/p files getting generated in the shared path , with the header created in one of the o/p file and the data records divided among these two o/p files equally.Now if i deploy the INT stored procedure version in dev env then multiple o/p files are again generated :( and i have compared both the version of stored procedure and they are same... :(I'm not sure why the script component is generating multiple o/p files.:unsure:Please advice.ThankssamTue, 01 Jan 2013 06:41:10 GMTsam 55243