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
How could I remark my script component, It is isolated, I just wanna check something without losing the long script written inside.
Thanks,
Fahad
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
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 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.
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.
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?
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!
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
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
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.
View 3 Replies View RelatedHi,
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.
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.
i 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.
Hi, newbie here with a simple?(maybe)question.
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.
View 2 Replies View RelatedI 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.
Facility: 4, Severity 1,Code: 1176, HRESULT:0x80040498
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...
Please suggest me some solutions....
Thanks in advance,
Kamalesh D
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
View 1 Replies View Related
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?
I have a student table that needs some clean up. My first task is to remove all the periods (.) from the middle name column. Some people have two char middle initials with two periods. Can this be done via a SSIS package (I sure it can, but not how)? I can't think of a simple update statement that would accomplish the same thing.
Any direction would be appreciated.
Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,
Hello All,I have a SQLDataSource "sdsX" which has four records. There is a DataList "dlX" which is binded to this SQLDataSource "sdsX". Now I want that my datalist "dlX" take third record of "sdsX" as its first element. Is there any property which can be used.Thanks
View 2 Replies View RelatedI am creating a DTS package for an import of data from MSAccess97 to SQLServer2000. I am quite new to the DTS so bear with me please.
Everything was fairly simple until I got stumped by the following problem:
In the source database there was a table with multiple fields (let's say A, B, C) for each record containing their values (let's say 1,2,3).
Now, in the destination database the table is built differently. It is a table containing the field data and definition.
So basically I need to turn this
ID A B C
0 1 2 3
1 4 5 6
into this
ID FieldName FieldVal
0 A 1
0 B 2
0 C 3
1 A 4
1 B 5
1 C 6
I am not sure how to go about that... any thoughts? :confused:
Hi,
I am having a simple difficulty regarding my transformation component that I have created.
I followed all of the relevant documentation to create the component, UI and such, and when I run a program that transfers data through my component (without it doing anything to the data) to a flat file source, all I get in the flat file is ,,,,,,,,,,
Meaning that the data is not actually being passed through my component to the destination, but it does recongize the correct number of columns. I get this warning for each column: [DTS.Pipeline] Warning: The output column "PurchaseOrderDetailID" (20) on output "OLE DB Source Output" (11) and component "Source - PurchaseOrderDetail" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
To fix this issue, all I need to do is "check" the boxes in the advanced editor for my component. However, I want to be able have these boxes "checked" automatically.
My question is, when you "check" a box next to a column name in the advanced editor, what does that exactly do that allows you to transfer the data? What do I need to program in order for it to replicate that? I actually want it to happen automatically, so by default, all data in all columns are transfered through my component out the other side, so I just need to know how to do this by code, and not how to replicate the UI.
My component, I thought, did that automatically, as I specified everything that I thought was required based on all of the documentation I read. Obviously, I am missing something. Here are the methods that I believe would all be involved.
Please let me know what I am missing.
int[] inputColumnBufferIndexes; // ...
int[] outputColumnBufferIndexes; // ... used in PreExecute
PipelineBuffer outputBuffer; // used in ProcesInput
public override void OnInputPathAttached(int inputID)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vCol in vInput.VirtualInputColumnCollection)
{
IDTSOutputColumn90 outCol = output.OutputColumnCollection.New();
outCol.Name = vCol.Name;
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
}
}
public override void PreExecute()
{
//base.PreExecute();
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
inputColumnBufferIndexes = new int[input.InputColumnCollection.Count];
outputColumnBufferIndexes = new int[output.OutputColumnCollection.Count];
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
IDTSInputColumn90 column = input.InputColumnCollection[x];
inputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
}
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn90 column = output.OutputColumnCollection[x];
outputColumnBufferIndexes[x] = BufferManager.FindColumnByLineageID(output.Buffer, column.LineageID);
}
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
//base.PrimeOutput(outputs, outputIDs, buffers);
if (buffers.Length != 0)
{
outputBuffer = buffers[0];
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
//base.ProcessInput(inputID, buffer);
if (!buffer.EndOfRowset)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
while (buffer.NextRow())
{
// TODO: Examine the columns in the current row.
// Add a row to the output buffer.
outputBuffer.AddRow();
for (int x = 0; x < inputColumnBufferIndexes.Length; x++)
{
// Copy the data from the input buffer column to the output buffer column.
outputBuffer[outputColumnBufferIndexes[x]] = buffer[inputColumnBufferIndexes[x]];
}
}
}
else
{
// EndOfRowset on the input buffer is true.
// Set EndOfRowset on the output buffer.
outputBuffer.SetEndOfRowset();
}
}
I am trying to use a CTE in an OLE DB Command data flow transformation object. However, when I enter the cte and corresponding query in the SqlCommand field of the OLE DB command editor dialog, I get a syntax error. Can CTE's be used data flow objects? I have been able to use them in an Execute SQL Control Flow Item, but not in any data flow item.
Does anyone know how to install or make available the Data Transformation Project Template in SQL Server 2005? I can not find it using Integration Services.
View 1 Replies View Related