Pass Data In Script Component When No Transformation Needed
Mar 26, 2007
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 ?
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.
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:
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.
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?
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.
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 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"
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'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.
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?
In my Data Flow Task I have a Fuzzy Lookup transformation. In the Columns tab of the Fuzzy Lookup Transformation Editor, if I attempt to select a field for pass through that is a DT_TEXT data type, I get the error:
Validation error. Data Flow Task: Fuzzy Lookup [3532]: The data type of column 'event_list' is not supported.Package.dtsx
BOL says, "Only input columns with the DT_WSTR and DT_STR data types can be used in fuzzy matching...." But I'm not doing fuzzy matching on the DT_TEXT column, I'm just trying to pass it through to the transformation's output. BOL doesn't say anything about this data type being incompatible with passing through to the output.
Any thoughts on how I may workaround this issue? I was thinking I would need to perform the lookup on a subset of the columns without the DT_TEXT field and then merge the data back together at the end. But, if there's a setting or some other way, please let me know.
The documentation on the fuzzy lookup transform mentions that only columns of type DT_WSTR and DT_STR can be used in fuzzy matching. I interpreted this as meaning that you could not create a mapping between an input column of type DT_NTEXT and a column from the reference table. I assumed that you could still have a DT_NTEXT column as part of the input and mark this as a pass through column so that it's value could be inserted in the destination, together with the result of the lookup operation. Apparently this is not the case. Validation fails with the following message: 'The data type of column 'fieldname' is not supported.' First, I'd like to confirm that this is really the case and that I have not misinterpreted this limitation.
Finally, given the following situation
- A data source with input columns
Field_A DT_STR Field_B DT_NTEXT
- A fuzzy lookup is used to match Field_A to a row in the reference table and obtain Field_C.
- Finally, Field_B and Field_C must be inserted into the destination.
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
Can anyone please point me in the right direction?
What I am trying to do should be very straightforward:
Take a flat file, perform various transformation on various columns using the SCRIPT COMPONENT task, then send the transformed (and un-transformed) rows to a table in the database.
My question is, how to do this using scripting? I have yet to see an example of what I'm trying to do. (I have both Kirk Haselden's book, Donald Farmer's SSIS scripting book, and the msdn website, but I have yet to see an example of what I'm trying to do!)
FILE SOURCE --> SCRIPT COMPONENT (synchronous transform) --> OLE DB DESTINATION
How do I account for all the columns that will be both transformed and un-transformed, and get them into the table? That is the missing piece of information I can't find anywhere.
The closest thing I found was this code snippet. Do I need to use this syntax, eg. Me.Output0Buffer.FirstName = (where FirstName is the actual column name??)
etc.
Then, once I hook up the SCRIPT COMPONENT to the OLEDB Destination, which uses a connection manager to the table, it will insert FirstName with what I specify?
I am trying to use a merge transformation task and receiving an error that I don't know how to troubleshoot further. Could I please have some advice on what else to look at to try to resolve the problem.
The error message text is: Error at Data Flow Task [Merge [1245]]: The metadata for "input column "LOCATION" (5451)" does not match the metadata for the associated output column
I have looked at the metadata and cannot see any differences: the following is output from the data flow path. Name Data TypePrecisionScaleLengthCode PageSort Key PositionSource Component ACCOUNT DT_STR 0 0 6 1252 1 Sort - FinSysData PROGRAM DT_STR 0 0 6 1252 2 Sort - FinSysData LOCATION DT_STR 0 0 6 1252 3 Sort - FinSysData PROJECT DT_STR 0 0 6 1252 4 Sort - FinSysData SUBPROJECTDT_STR 0 0 2 1252 5 Sort - FinSysData ACTIVITY DT_STR 0 0 6 1252 6 Sort - FinSysData FUNDING DT_STR 0 0 3 1252 7 Sort - FinSysData CLIENT DT_STR 0 0 6 1252 8 Sort - FinSysData NTWAGE DT_STR 0 0 3 1252 9 Sort - FinSysData TYPE DT_STR 0 0 1 1252 10 Sort - FinSysData PERIOD DT_STR 0 0 6 1252 11 Sort - FinSysData CO DT_STR 0 0 2 1252 12 Sort - FinSysData FIN_YEAR DT_I4 0 0 0 0 13 Sort - FinSysData BALANCES DT_R8 0 0 0 0 14 Sort - FinSysData
Hi I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system, then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that was read, else send the data down the unmacthed output which will be the same as the input.
So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows load in memory.
Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer, as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.
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.
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.
My vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
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?
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?
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
I have an Access Database that I have imported into SQL Server2000 and that worked great, but now I have to get it into 2005. My question is, How can I get the tables and all info in the tables into an SQL Script so I can run that script on the 2005 server?
The SQL 2000 is on my dev server and I have all the Tools, (Ent Manager, Query Analyzer,etc...) but the 2005 Server is Godaddy's and they only have the basic web interface. I can run Sql files and create databases and tables, but thats about it.
HiI was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.I have tried to install DTS with no success. I am running the followingVisual web studio expressSQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra componentsInstalled SQLEXPR_Toolkit.exe with all its optionsInstalled SQLServer2005_DTS.MSI When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.Can somebody please help me.
I have begun using SSIS and I am a little taken aback by the complexity of it especially since I just want to do a simple data transformation such as in DTS. Are there any tutorials for data transformation for SSIS on the web/this forum and what if I want to do a simple transformation from Access to SQL Server?
I tried transforming data from one server to another using DTS. and then i got an error as below, ----------------------------------------------------------------------------- Details: Error Source: Pump Data Step Details: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Desc: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Source : Microsoft Data Transformation Services(DTS) Package
Source : Microsoft Data Transformation Services(DTS) Package Code: 0x80040428 Description: Package failed because step'Pump data step' failed. Error Message: IDispatch error #552 --------------------------------------------------------------------------
This is the full description of the error dialog...
I'm running a DTS package on SQL Server. The source is MS Access and thetarget is Oracle.On a "Drop Table" command the process just hangs. There are no foreign keys onthe table. Several tables have already been processed successfully by thistime.I think I've ruled out corruption by dropping and recreating the targetdatabase on Oracle.Any ideas?M Man
I have a lookup transformation that retrieves a key for a certain column of values, in this case, a name. So, I go in to the lookup table with a name and come out with its key. I had it working and then I added new entries to the lookup table for a bunch of new names. Now, for some reason, I am not getting the matches for the new names. But I am still getting the matches for the names that existed before I added the new ones.
I'm wondering if the lookup transformation is using the old set of data and some how not picking up the new names. Do I have to trigger something in the lookup transformation to let it know that the lookup table data has changed?
