SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with script component when used as destination


Issue with script component when used as destination

Author
Message
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
Hi all,

Need help in solving an issue.

i have a SSIS pkg deployed in INT & Dev env respectively. The data flow is like this

One 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 Sad and i have compared both the version of stored procedure and they are same... Sad

I'm not sure why the script component is generating multiple o/p files.Unsure

Please advice.


Thanks
sam
Attachments
1.jpg (11 views, 130.00 KB)
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51827 Visits: 21170
Can you share the code inside the Script Component?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
Hi Phil,

Please find the script component code below....


Option Explicit Off
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports 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 Sub

End Class




Thanks
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51827 Visits: 21170
I'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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
Hi Phil,


Can you tell me the file names of the two files which are generated please? Are they created at the same time?


The files that are generated are of different time stamp, they differ by few minutes/sec apart.

Thanks
Sam
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51827 Visits: 21170
Is 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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sam 55243
sam 55243
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 953
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:

Option Explicit Off
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Text

Public 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 Sub

End Class




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 idea



Thanks
Sam
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51827 Visits: 21170
sam 55243 (1/2/2013)
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:

Option Explicit Off
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Text

Public 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 Sub

End Class




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 idea



Thanks
Sam


Hi Sam

I 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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search