In the code behind the Script Transformation component, neither the MsgBox nor any of the breakpoints seem to work. Am I missing to do something in order for these to get executed?
Thanks.
Andy.
Does anybody know if the breakpoints are supported in the script component?
I've got a script that I want to debug and make sure it's doing what it should be but everytime I set a breakpoint it loses them when I click ok on the script editor. I've tried setting precompile to false but thats not working. What do I need to do to be able to use breakpoints?
Saitham8
Hi,
I'd like to perform an update on a database only when certain conditions are met. Hence, one of those conditions would be a positive answer from the user running the query. Basically, I'd like to display a message with a yes/no option for the user to choose from. If and only if the user user selects the YES option should we perform the update.
Inside a transact SQL "IF ... THEN", how can I achieve this?
Thanks,
SC
I have an SCD that contains a historical output path. If I throw dataviewer in the flow before the SCD, I can see that it should trigger a trip down that lane, but its not. In the advanced editor, all looks good. Anything I can check. BTW, the datatype of the fields that should cause the SCD are datetime.
thanks in advance
I have few tables that I need to export to an MS Access database each day to send off to the customer. In these tables I have a few columns that I need to strip all the HTML out of before they are put into Access since Access does not display the formatting correctly. Some of the fields are varchar and some are text, but all of the fields that are varchar are over 255 in size, so this forces me to use Access' Memo type for both.
In SQL 2000 I used the ActiveX Script to modify these fields, stripping out the HTML, and it gave me no complaints. In SQL 2005, I am adding a Script Component into the Data Flow before the Destination and adding the script in there. So far so good. The problem arises when I try to retrieve and update the data in these fields because they are treated as BLOB data. I believe what I need to do is to retrieve the Byte array from the row using the GetBlobData(), then convert that to a string, strip out the HTML, convert back to a Byte array, then clear the original value using the ResetBlobData(), and then update the field using the AddBlobData. I think?
I am not even sure that my code below is converting the byte array into a string correctly. If I throw a Messagebox in there to see what it has for the string, it just gives me the first character in the field. But even ignoring that, the package will not execute and I am stumped.
The error I now get is:
Array cannot be null.
Parameter name: bytes
Code Block
''-----------------------------------------------------
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim b As Byte()
If (Row.ProjectDescription.Length > 0) And (Not (Row.ProjectDescription_IsNull)) Then
b = Row.ProjectDescription.GetBlobData(0,CInt(Row.ProjectDescription.Length))
End If
Dim str As String
Dim enc As New System.Text.ASCIIEncoding()
str = enc.GetString(b)
str = RemoveHTML(str)
b = enc.GetBytes(str)
Row.ProjectDescription.ResetBlobData()
If b.Length > 0 Then
Row.ProjectDescription.AddBlobData(b)
End If
End Sub
''-----------------------------------------------------
how to evaluate working day through script component in SSIS
I have a data source. I need to verify if the date belongs to working day, if it does belongs to holiday or weekend ,then I need to send that row to the out put table. I think the only way I can verify this working day issue through script component. Can any one give me some idea/thoughts?
You all have a wonderful weekend.
Thanks
I have few tables that I need to export to an MS Access database each day to send off to the customer. In these tables I have a few columns that I need to strip all the HTML out of before they are put into Access since Access does not display the formatting correctly. Some of the fields are varchar and some are text, but all of the fields that are varchar are over 255 in size, so this forces me to use Access' Memo type for both.
In SQL 2000 I used the ActiveX Script to modify these fields, stripping out the HTML, and it gave me no complaints. In SQL 2005, I am adding a Script Component into the Data Flow before the Destination and adding the script in there. So far so good. The problem arises when I try to retrieve and update the data in these fields because they are treated as BLOB data. I believe what I need to do is to retrieve the Byte array from the row using the GetBlobData(), then convert that to a string, strip out the HTML, convert back to a Byte array, then clear the original value using the ResetBlobData(), and then update the field using the AddBlobData. I think?
I am not even sure that my code below is converting the byte array into a string correctly. If I throw a Messagebox in there to see what it has for the string, it just gives me the first character in the field. But even ignoring that, the package will not execute and I am stumped.
The error I now get is:
Array cannot be null.
Parameter name: bytes
''-----------------------------------------------------
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim b As Byte()
If (Row.ProjectDescription.Length > 0) And (Not
(Row.ProjectDescription_IsNull)) Then
b = Row.ProjectDescription.GetBlobData(0,
CInt(Row.ProjectDescription.Length))
End If
Dim str As String
Dim enc As New System.Text.ASCIIEncoding()
str = enc.GetString(b)
str = RemoveHTML(str)
b = enc.GetBytes(str)
Row.ProjectDescription.ResetBlobData()
If b.Length > 0 Then
Row.ProjectDescription.AddBlobData(b)
End If
End Sub
''-----------------------------------------------------
I have a timestamp column and a username column with default values of getdate and system user in my table and they are defined as not null.
even though i donot use these columns in scd type 2 in wizard
if the columns are not null the scd deosnot work and If the columns are defined null they work
Can anyone please explain or help me with what might be the problem associated with this
I have a blob which is essentially an email and I need to replace all carriage returns in the blob with a special character ("€°"). But it does not work. Please advice.
Dim intBlobLen As Integer = Convert.ToInt32(Row.body.Length)
Dim intFinish As Integer = intBlobLen - 1
Dim bytBlob(intFinish) As Byte
bytBlob = Row.body.GetBlobData(0, intFinish)
Dim strBlob As String = System.Text.Encoding.Unicode.GetString(bytBlob)
strBlob = Replace (strBlob,"/n","€°")
If strBlob.Length > 0 Then
Row.body.ResetBlobData()
Row.body.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
End If
Please help. Thanks in advance
Hi,
I didn't play around with breakpoints to much, yet. So today I created a package with 2 variables, which both raise their change event. I added a event handler for that just to show the name of the variable which initiated that event (system::variablename) in a messagebox. I added two scripts to the main workflow, one changes one variable, one the other.
To see what's happening I placed a breakpoint on the script in the event handler. I start the package, the first script is executed, the event is fired and the script is started. The breakpoint stops executing the package. So good so far. When I now click on "continue" the script task in the event handler changes to "yellow" and I hear a beep. That's all. I don't see any other action in the package, the second script is not started and the run seams to be locked.
What's wrong? Is the problem sitting in front of the monitor? Am I the 1000th person hitting that bug?
Thanks,
I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:
SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.
If you were in the middle of something, the information you were working on might be lost.
Debug Close
When I choose "Close" I get this:
sMicrosoft Visual Studio for Applications has lost the link to .
Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.
OK
When I click "OK" my package executes normally, but no breakpoints will fire from that point on.
When i want to debug the stored procedure in SQLServer2000 query analyzer
the stepInto button gets disabled so it executes sp rather than debug.
Exec sp_sdidebug 'legacy_on' is not working.
Thanx in advance
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,
2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.
Thanks in advance.
How can we get the name of the component inside the Data Flow Task . What I want is to log error stating which component in the data flow task has failed. Package and Data Flow names I am getting from system variables. I want to log like the Execution Result screen with Name of the component and [its id].
Like "Derived Column[216]" has failed with some error
It is possible?
I'd like to incorporate the "package component tree" UI component that is used within the Visual Studio designers into an SSIS utility I'm building. This is the one I'm talking about:
Edit: Apparently using the IMG tag on these forums works in the editor preview, but not in the actual posts, so I've replaced images with links...
Example 1
Example 2
I've done some searching online, but have not found any information about where this UI is implemented, or if it is reusable. Does anyone here know if it is possible to re-use this component in a .NET application?
Thanks in advance!
Hi, all.
I create a report using VB datareport.
but, when I call following statement.. it cause Error "Class not registered!"
cnn.Open "Provider=MSDataShape.1;Persist Security Info=False;User ID=" & gstrID & ";Password= " & gstrPassword & ";Initial Catalog=TimeClock;Data Provider=SQLOLEDB.1" & _
"Data Source=" & gstrLocalDBServer & ";server=" & gstrLocalDBServer
How can I solve this problem?
Ok. I have 2005 standard with sp2 installed. Went to create a maintenance plan and low and behold the component is missing. Ok.. no big deal, i'll just add it. Of course you know this is the error I get.
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Ok.. doesn't seem too bad. So I go to a dos prompt. d: oolssetup.exe skuupgrade=1
Same error.
Help please :)
Hi,
While using 'Script Component' in SSIS, how to use a user-defined variable created at the package level in the script?
Hi,
I'm trying to add an icon to a custom component but its not happening and I can't figure out why. I'm sure I've managed to do this in the past. Here's my DtsPipelineComponent attribute:
Code Snippetnamespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="MyComponent.ico"
)
MyComponent.ico is stored in the same folder as my project (i.e. at the same level as the .cs file and the C# project file).
In the application property page I've pointed the icon resource at MyComponent.ico
Everything compiles OK. But I see no icon in my component when I deploy it to the toolbox or use it in a package.
I've also tried changing the attribute to this:
Code Snippetnamespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="Jamiet.Ssis.MyComponent.ico"
)
Again, it compiles OK, but no icon!
Any ideas what I'm doing wrong?
Thanks
Jamie
Hi all,
I'm creating a custom interface for reporting services but I am having a few problems. This is my environment:
Using the ReportViewer component in remote mode to show my reports of the report server. There are 2 user levels of which any user can be apart of, Admin & user. In every report, there is a parameter called MERCHANTID, when the user who logs in (via custom login interface) is of group admin, then the merchantid parameter gets prompted before the report is run, if the group of the user is "user" then the merchantid parameter is hidden, and is passed programatically to the report. The report path also
My Questions:
1. When my reportviewer component loads up the report and prompts for a parameter, when you select a parameter value or type it in, it just posts back with no results, just the parameter prompt again with no data... How can this be corrected?
2. How do I pass report parameters programatically... so that when my user id of group "user" then "merchantid" gets passed automatically, but if there is any more parameters, then that gets prompted...
Thanks for your help.
i have 2 source columns testsource and testsource1 and 2 output columns
test and test2
i am passing them through a script component to check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column
Is it that the script component can only look at one column?
this is the code i wrote
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If IsNumeric(Row.testsource) Then
Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)
Else
If Row.testsource = "N.A" Then
Row.test = "NULL"
End If
Row.test = "NULL"
End If
If IsNumeric(Row.testsource1) Then
Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)
Else
If Row.testsource1 = "N.A" Then
Row.test2 = "NULL"
End If
Row.test2 = "NULL"
End If
End Sub
End Class
example of run
INPUT
,20070930
,20080331
,20070930
,20071130
,20070930
,
,
,
20070504,20070503
20080331,
,20070930
N.A. ,N.A.
N.A. ,N.A.
OUTPUT
NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.
as you can see the first column gets evaluated based on logic but the second column just gets passed through..
Thanks for any help in advance
smathew
Hi,
I have an XML data file and an associated XSD file with properly defined datatypes. However, the datatype of all the data elements are always "string" datatype. For example, in my current xml file, all the data elements are of Decimal datatype which is properly defined in XSD file. However, datatype of all the output columns are of string datatype.
Is it a bug or am I doing something wrong?
Thanks
Navnish
Hi,
View 19 Replies View RelatedExists any easy way to renew component GUID without having to recreate the component?
Hi,
I have several SCD components in my project. As I have to process millions of records, SCD's are taking a lot of time. Is there a way to speed them up? Work arounds?
Any tip is welcome
-Tom