Performance Issue With Custom Data Destination Component
Feb 12, 2008
Hi,
All the examples I've seen for creating custom data destinations (scripts or components) show that in the ProcessInput method you need to iterate through each record and execute a commit for each row. Here's a sample from http://forums.microsoft.com/msdn/showpost.aspx?siteid=1&postid=70469
Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
With sqlCmd
.Parameters("@addressid").Value = Row.AddressID
.Parameters("@city").Value = Row.City
.ExecuteNonQuery()
End With
End Sub
This works for me but is extremely slow when processing lots of rows. Has anyone come across a better/faster example for committing rows to a database when creating a custom data destination component? I was thinking about using the ADO.NET batch update. Any thoughts on this or other approaches?
I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package.
Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message?
Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?
I am writing a Custom Destination component with a custom UI. The UI contains a combo box which contains the connection names of type €œFLATFILE€?. I also have provided a button which would create a new connection of type €œFLATFILE€? by making a call to CreateConnection method of IDtsConnectionService. The combo box gets properly updated showing the connections of type €œFLATFILE€? but on clicking on the new Connection button the application hangs up. Am I missing something or is there some other way to do it?
The function are the events handlers which are called by the UI.
if (connectionService != null) { ArrayList temp_Connections = connectionService.GetConnectionsOfType("FLATFILE");
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count]; for (int i = 0; i < temp_Connections.Count; i++) { ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections; args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection); }
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count]; for (int i = 0; i < temp_Connections.Count; i++) { ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections; args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection); }
I am running SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1. Any suggestions would be welcome.
The code sample is an extension to the RemoveDuplicates sample (Dec 2005) which comes along with the SQL Server.
I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C: empfile.txt Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows. SSIS package "Package.dtsx" finished: Success.
I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.
This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?
Is there a default destination component used when a new data flow is created? The reason I ask is simply curiosity. I have an xml file with 2 pieces of data: item A and item B. A should simply get copied out of the file. B should undergo a quick transform. I set up an XML source such that two columns are mapped correctly to the XML source data of A and B. I set up my data transform task as well. So, if I leave those two components on the .dtsx page with no other components, then will there be a default data flow destination already created? ...OR, do you always have to have a destination component?
I am looking for some advice regarding saving custom component data when saving packages.
For custom "properties", this is not an issue, as saving the package will save these properties. Howver, I also have information for each column (besides the properties that columns provides, like Name and DataType) that I need to save if a package were to be saved, and right now it does not save because I am using my own objects to store the data.
I am wondering as to how I can save this information. I have looked up on serialization, but I would like to know if there is another way besides serialization to save this inforamtion as I'd rather not save this to a seperate file.
I'm having my first go at developing a destination adapter which will send data to an update Web Service.
I've got some rather big gaps in my understanding. I've been following the various samples I've found on the net and have validated my mapping and picked up all the available column names and datatypes which are appearing in the Input and Output Properties tab of the Advanced Editor but I only have a tab for "Input Columns" and not "Column Mappings".
Which method defines the availble columns for the user to map?
Let me know if I haven't given enough information.
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 creating a customer data flow component for SSIS for use in a package. I've got some custom properties that I am exposing using the supplied advanced editor (no custom property editor here).
Some of my properties are enumerated types, and I have deciphered how to get those properties to show as dropdown lists of their respective enumerations. (For those of you who may be looking as hard as I did as to how to accomplish this, see the end of this post.)
I also have a few properties which request SSIS package variable names - such as an file name variable. However, I can't figure out how to tell the advanced editor that the property is looking for an SSIS variable, so that it can show a dropdown list of package variables, much like virtually any other Microsoft supplied Data Flow component can.
Is there a Type Converter I could specify for those custom properties? Is there another way to instruct SSIS that my custom property is expecting a variable? Or do I need to code a custom UI for editing my Data Flow Task?
To create a dropdown list of values for a custom property that represents an enum, do the following:
1. Create your enum definition, such as "public enum ThisIsMyEnum { one, two }"
2. Create a new class that inherits from TypeConverter, such as "public class MyEnumConverter : TypeConverter"
3. Override "CanConvertFrom", and return true if "sourceType == typeof(string)"
4. Override "CanConvertTo", and return true if "destinationType == typeof(string)"
5. Override "ConvertFrom", and return the enum value (such as "one" or "two" in my example) that corresponds to the string passed in the parameter "value"
6. Override "ConvertTo", and return a string that corresponds to the enum value passed in the parameter "value"
7. Override "GetStandardValuesSupported" and return true
8. Override "GetStandarValuesExclusive" and return true to indicate that ONLY the enum values should be accepted
9. Override "GetStandardValues", and return a new StandardValuesCollection constructed with Enum.GetValues() of your enum, such as "return new StandardValuesCollection(Enum.GetValues(typeof(ThisIsMyEnum)));"
10. Just above your "public enum" declaration, add a "TypeConverter" attribute to link your type converter to your enum, such as "[TypeConverter(typeof(MyEnumConverter))]"
11. In "ProvideComponentProperties", after you've created your custom property like this: "IDTSCustomProperty90 propEnum = ComponentMetaData.CustomPropertyCollection.New()", add another line to specify the TypeConverter property of the property to the full assembly name of your type converter, like so: "propEnum.TypeConverter = typeof(MyEnumConverter).AssemblyQualifiedName;"
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.
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
when loading the transformed data into OLE DB destination, there is no options to truncate destination table first. Have to insert a middle step to run script to truncate the destination table first.
I'm very confused. We even has the options of keeping or deleting the data in destination table in SQL2000 DTS package. Why we don't have this option in SQL2005??
I am creating and running a package programmatically. I have the source component set up fine, and the destination component seems good, but when the package is run, it gets the error message: "Excel destination failed validation and returned validation status "VS_NEEDSNEWMETADATA" ". This would lead me to believe that I need a ReinitializeMetaData() call, but I already have that (see below). How do I fix this? Thanks for your help.
' Create and configure an OLE DB destination.
Dim conDest As ConnectionManager = package.Connections.Add("Excel")
Hello, I am trying to create a simple package programmatically. I am following the examples in the BOL, and from some advice here. I am getting stuck at creating an Excel Destination and setting its sheetname. Everything works fine, including setting the output Excel filename. I get a runtime exception when I try to set the sheetname via SetComponentProperty. Is there another way, or am I doing something wrong? Thanks for any info you may have.
' Create and configure an OLE DB destination.
Dim conDest As ConnectionManager = package.Connections.Add("Excel")
Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.
Is this possible in SSIS?
The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.
Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.
I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.
I have an SSIS package that produces an Excel output file say File1 The Excel output file is created from a previous script task by copying a standard excel template to File1 after the copy, File1 has the disclaimer, legend etc... and a header row at row 10. So data rows should only start at row 11.
I was googling and found that people who read Excel file using an Excel source component have been successful reading from a range by the use of the OPENROWSET property. It is said to set this property to Sheet1$A5-B999 to start reading from row 5.
I tried to set the OPENROWSET property to be Sheet1$A11-B999 but am getting an error "Check that object exists" (Sheet1 does exist) So I guess it is the range that has an improper syntax or something else is wrong.
I am writing a custom transformation component that utilises a user variable.
Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.
Is there a way to add a user variable with package scope from a custom component, either at run time or design time?
I've run into this a second time now. I'm hoping for some resolution AND guidance for proper build, save, etc. protocols in BIDS.
I've coded a custom component, included post-build procedures to add it to the GAC, selected it from the "Choose Items..." menu, and successfully added to a data flow. I've then been able to succesfully debug it.
This was all Friday afternoon. I shut down my BIDS session(s) and called it a week. This morning when I open BIDS, I get the following error when I attempt to add it to a data flow task:
TITLE: Microsoft Visual Studio------------------------------The component could not be added to the Data Flow task.Could not initialize the component. There is a potential problem in the ProvideComponentProperties method.------------------------------ADDITIONAL INFORMATION:Error at Data Flow Task [DTS.Pipeline]: Component "component "" (16)" could not be created and returned error code 0x80131600. Make sure that the component is registered correctly.------------------------------Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------BUTTONS:OK------------------------------
What would explain this? I'm new to VS/BIDS. Am I improperly shutting down my BIDS sessions? Is a save required for my code? Does "Build <component>" not automatically save?
Also, when I re-build a component after making a change to it, what do I need to do in my package's Data Flow task? Delete the current component, re-add it, and configure it again? I'm sure there are certain things that require one type of re-initialization vs. others (i.e., making a change to the Design Time code vs. making a change to code within the ProcessInput method.)
What can I do to properly register this now? I'm dead in the water and have no idea what to do. Any help's appreciated.
I developed a €œdata flow source€? in my computer and it is ready! It works in my computer, but how do I deploy this component in to the server? I have any programming environment on it. Any Idea?
I was trying the SSIS programming samples,that comes with installation of SQL Server 2005, I want to load the ChangeCase DLL in the toolbox.
The readMe file gives the direction. It does not work. It says you will see component in once you see DataFlow Task button( clicking Choose Items in the tools box).
I cannot load the custom component to the toolbox, Can I get some help.
in the acquireconnection method Using the below statment I can get a connection Object
oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
from the connection object I can get the connectionstring from the object by calling
oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.
How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.
Hi,I have developed a custom source component to get data from a generic odbc connection with some special caracteristics.
The component works fine by getting and mapping the output fields etc...
The only two problems existing are that when i run the task it says that the data flow has no components inside... how is this possible since i have my source mapped to a flat file inside that data flow?
This is the error:
SSIS package "BVEIT000D.dtsx" starting.
Warning: 0x80047034 at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.
Information: 0x4004300C at BVEIT000D_<EMPRESA> TXT, DTS.Pipeline: Execute phase is beginning.
SSIS package "BVEIT000D.dtsx" finished: Success.
The other problem is that if i want to <ignore> a certain source column the component already shows me an error saying that the no column with ID 0 was found...
Any one with experience in creating custom components?
Hi huys, can you tell me how you create a MSI file for your custom component.
basically i've created a SSIS custom component, and i want to distiribute among a few people, i guess instead of providing step by step instructions it would be easy if they just clicked on packaged msi file.
i've seen many people putting their custom components on the web as MSI, how do you do it?
Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?
