Hi all,
I am missing something simple. I have added a new Transformation Script, put in my code to read the input rows, defined my outputs. I have tried to change the SynchonousInputId to 0, but I only get the option of None or input "Input 0" (91). What have I missed?
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
Hi,
I'm on writing a custom data flow component (transformation). For this I need access to an external datasource. As best practice, it seems that you should use a connectionmanager in the package.
But I don't really know how to access them.
I've overwritten the AquireConnections() to lookup in the RuntimeConnectionCollection for the connection managers. But It seems it doesnt get called. If I call it my own, for example in validate (just for debugging purposes) the collection is empty, although there are two connectionmanagers in the package.
What am I doing wrong or has someone a code snippet for me.
Thanks T.
i am developing one custom transfer component, where i am building one custom object and want the same to be transfered from ComponentUI to component.I explored in this issue and came to know that we can make use of SaveToXML and LoadXML methods of IDTSPersist90 interface. The problem is i could not able to make use of this interface.If any body faced same issue and got the solution, let me know the same.
Thanks in advance
Karun
Hello,
Using the following documentation as a guide:
http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx
I instantiated a new script component into an existing Data Flow in my SSIS project.
In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.
My Connection Manager is of the type oOLEDB.
I then opened up the script designer and added the following lines of code where it said "Add your code here"
Dim myConnManager As IDTSConnectionManager90 = _
Me.Connections.ECFconnection
Dim dbConn As OleDb.OleDbConnection = _
CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)
When I test run the project I get the following error and the new script component is red:
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
I know the database connection works since I am using it in a component that executes before this new script component.
I am stuck...Any suggestions?
I've been trying to figure this out on my own for pretty much all of today, and part of last week. I've downloaded samples, searched this forum, blogs, etc. So I figured I would post, since it's the end of the day, and I'm not much further along.
I'm working on a custom transformation component, whose main function is to use SQL encryption/decryption to encrypt/decrypt data from the input columns, into the output columns. The component needs two strings, a key name and a certificate name, as well as the connection manager it should use to connect to SQL which will do the encryption/decryption.
Here's where I'm stuck:
1) How can I provide the key/certificate names via properties? What I'm expecting/looking for is a way to add these two properties at the component-level, which would show up under the "Custom Properties" section of the properties pane (currently, this only has one property, "UserComponentTypeName"). These key/certificate values will be used for all input columns.
2) How do I access the connection managers from within the component? What is the best way to go about using a connection manager from within my component to connect to SQL and perform the encryption/decryption? In a custom task, this was fairly simple, but it seems that same concept won't work on a transformation component.
3) Is there a better way to go about accomplishing this (column encryption via SQL from within SSIS)? Am I going about this all wrong?
As I said, I've searched for direction, but there seems to be next to nothing in the regards of a good reference for creating custom transformation components. I've looked at two MS samples, but can't seem to make any sense out of them.
Thanks in advance.
Jerad
Hi all,
Is there any tutorial to learn how custom transformation component works? maybe a blog, pdf or something...
Specifically, i need to learn how to generate an output column composed from 3 input columns. The problem is i dont know how to set the column value... anyone have some sample code?
Thanks!
How could I remark my script component, It is isolated, I just wanna check something without losing the long script written inside.
Thanks,
Fahad
Does anyone have any examples of programmatically creating a Transformation Script Component (or Source/Destination) in the dataflow? I have been able to create other Transforms for the dataflow like Derived Column, Sort, etc. but for some reason the Script Component doesn't seem to work the same way.
I have done it as below trying many ways to get the componentClassId including the AssemblyQualifiedname & the GUID as well. No matter, what I do, when it hits the ProvideComponentProperties, it get Exception from HRESULT: 0xC0048021
IDTSComponentMetaData90 scriptPropType = dataFlow.ComponentMetaDataCollection.New();
scriptPropType.Name = "Transform Property Type";
scriptPropType.ComponentClassID = "DTSTransform.ScriptComponent";
// have also tried scriptPropType.ComponentClassID =typeof(Microsoft.SqlServer.Dts.Pipeline.ScriptComponent).AssemblyQualifiedName;
scriptPropType.Description = "Transform Property Type";
CManagedComponentWrapper instance2 = scriptPropType.Instantiate();
instance2.ProvideComponentProperties();
Any help or examples would be greatly appreciated! Thanks!
Microsoft published a "Creating a custom transformation component Walkthrough" published on
http://www.microsoft.com/downloads/details.aspx?FamilyID=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&DisplayLang=en
Does anyone know where to get the Hands-On Lab Files mentioned?
Thanks
Alex
Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.
The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?
Any input in this would be appreciated.
Thanks,
Hello Helpers,
I need to know how to use my private function - created as a scalar-valued-function in SQL Server 2005 - in script component (here a transformation is used) in a data flow task to transform a two-digit-month into a tree-sign-month:
Example: '01' should be transformed into 'Jan'
Many thanks for alle your commitment and help!
Ulrike
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
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
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
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)
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
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.
Hello I trying to do some transformation using javascript. I have a table with 40 columns that I need dynamicaly mapped to another table. For example I need to test each Column of tableA if Col1 of TableA looks like an email address map it to TableB email if it looks like a zip map it to Email...
I need to do this with Javascript
if someone could point me in the right direction that would be great
lets say I start small test each col in TableA for an @ sign if I find it map to Email if I can get the idea of how to do that I can do this
thanks
Hi there,
I want to check whether the values in a column within a defined list of values or not.
These values aren't in a table or a file - I just want to "code" them in the package so to speak.
I was thinking of using a script component but I'm not sure how to "fail" a row if it the column's value is not part of the pre-defined list.
Any suggestions.
Thanks.
plz tell me abt look up transformation , i need to look up with 3 source files ,1 flat file another 2 are compared with databases, how can we use that ....
plz tell me.
I am a bit familiar with SSIS, but I have not used it in about a year. However, this is my question. We have a couple of reports that are being done in SSRS and our customer also requires some of these reports to be exported to a flat file. Somebody suggested using SSIS to do this.
What transformation element should I use to transform the data to a flat file destination. For instance, I have a column with a value of 10250. The customer specifies that this column must be right-justified with leading zeros. So I need to take that and convert it to a field length that is 12 characters long. It would ultimately be 000000010250.
The data source is returning columns that are both numeric and strings.
Thanks for the help.
Hi,
we are trying to implement this logic in SSIS data flow task
We have 2 datasets
Dataset 1 has Emplid and Actiondate. (from Oracle Database instance I)
Dataset 2 has Emplid, ProjectID ,Project Start_Date ,Project_End_Date (from Oracle Database
instance II)
Logic is
select ProjectID from Dataset 2
where Dataset 1.Emplid = Dataset 2.Emplid
and Dataset 1.Actiondate between Dataset 2.Project Start_Date and Dataset 2.Project_End_Date.
for first condition we have used merge join between 2 datasets.
for second condition we are trying to use look up transformation... it did'nt help...
if i want to write an query where do i write that???
please help
Thanks in Advance.
I am trying to transform data from a text file to a SQL Server table and one particular column I want to split it to three other columns. It is erroring out when I try to split the column from the source in the Active X script while doing the transformation.
Is it possible to have more number of Columns in the destination when compared to the source.
If so can someone help me with this.
Thanks,
Hari
We are transferring data between AS/400 and SQL Server 7.0 using DTS. Some of these transfers may need to be very close to real time. It doesn't seem like a continuously running job is the best solution for that.
Do you know any tools or utilities that can help us to move the data?
Thank you,
Anastasia.
Please help,I am using SQL Server 7 and this is the first time I am using DTS. Can I import a text file with 3 fields to a table which has those 3 fields and also another 2 fields? Can somebody help with the ActiveX script to accomplish this? And one of the extra 2 fields in the table is a timestamp field. It needs to be timestamped automatically. Can somebody please help? Thank you in advance
View 2 Replies View Relatedi have something like this:
select * from accounts
name type amount
==== ==== ======
mary saving 123.00
mary chequing 246.00
mary investment 135.00
john saving 678.00
john chequing 987.00
john investment 0.00
what should i do to present the data in the following format?
name saving cheq investment
==== ====== ==== ==========
mary 123.00 246.00 135.00
john 678.00 987.00 0.00
Thanks.
I am importing user id's from CSV file into my Database. The source user id is of 4 character long and the destination user_id field is of 100 varchar. I just query the data so that i wan'a check that is said user id is already exist in the database or not . So i got this error. Read the script and error below
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************
' Copy each source column to the destination column
Function Main()
DIM v_user_id,rs, ConStr,sql
set rs = CreateObject("Adodb.Recordset")
set con = CreateObject("Adodb.Connection")
ConStr = "Provider=SQLOLEDB;Server=192.168.1.71;Database=tes tkaanza;uid=sa;pwd=sa"
v_user_id = DTSSource("Col001")
rs.open "Select user_id from tbl_gc_user_hdr where user_id = " + v_user_id,ConStr,0,1
if Not rs.eof then
MsgBox "Record Found"
else
MsgBox "Record Not Found"
end if
rs.Close
End Function
i got error from this line :
rs.open "Select user_id from tbl_gc_user_hdr where user_id = " + v_user_id,ConStr,0,1
Error message :
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:The conversion of the varchar value '1000015151910165' overflowed an int column. Maximum integer value exceeded.
Error Help File:
Error Help Context ID:0
Thank you in advance
What is the fonction to use in order to format a date to char?
for instance have the date under the following format :
ddmmyy or dd/mm/yyyy ...
Thanks
Hi,
I need to extract a table from someother database in which one of the city columns with datatype CHAR has city values but all the values are appended by dots at the end...like:
"Newyork.............."
"Boston..............."
"Chicago.............."
"Los Angeles..........."
After extracting the table/data , i will have to do some transformations and lookups on the table....and at the same time i will have to remove the dots and load the table/data in to another SQL Server Database, i dont think i can use the substring as each record's length is different.
Can someone suggest me how to do this one...?
thanks
Ravi
Nothing much that i can do..!!