How Do I Propagate Custom Properties To Downstream Transforms In A Data Flow Task
May 15, 2006
I implemented a custom source adaptor. I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.
View 1 Replies
ADVERTISEMENT
Aug 14, 2007
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
View 6 Replies
View Related
Jul 24, 2007
I have found not very good behavior of Union All Data Flow Component.
I have Script component and about 30 Union All Data Flow Components. I have string field named 'MyField'. Legth of the field is 15 characters. The field appears first in Script component (defined as 'MyField' Output collumn) and present in all Union All Data Flow Components. I need to change field length from 15 to 10. In Script Transformator Editor of Script component I changed 'MyField' Output collumn Length property value set it to 10. But length of the field remains 20 in all Union All Data Flow Component downstream and no errors no warnings appears. To fix this i recreated (deleted and added again) 'MyField' in all Union All 30 Data Flow Components.
If extend length from, say, 15 to 20 in the first of Union All Data Flow Components error sign appears
"Error 1 Validation error. Data Flow Task: Union All [22]: The metadata for "input column "MyField" (144)" does not match the metadata for the associated output column."
and only recreation of the field in all Union All Data Flow Components can help to fix it.
May be someone knows other solution to fix such behavior?
View 1 Replies
View Related
May 19, 2007
I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?
View 5 Replies
View Related
Jan 17, 2007
Hi,
I developed a custom data flow task in .net 2.0 using Visual Studio 2005. I installed it into GAC using GACUTIL and also copied it into the pipeline directory. This task runs absolutely fine when I run it on my local machine both in BIDS and using the script in windows 2000 environment. However, when I deployed this package into a windows 2003 server, the package fails at the custom task level. I checked the GAC in windowsassembly directory and it is present. Also I copied the file into the PipeLine directory and verified that I copied it into the correct pipeline directory by checking the registry. The version of the assembly is still Debug. I looked up documentation in MSDN but there is very little information about the errors I am seeing.
The error I get is pasted below, Can somebody please help me as I am currently stuck and running out of ideas to fix this problem.
Code: 0xC0047067
Source: DFT Raw File DFT Raw File (DTS.Pipeline)
Description: The "component "_" (2546)" failed to cache the component metadata object and returned error code 0x80131600.
Code: 0xC004706C
Source: DFT Raw File DFT Raw File (DTS.Pipeline)
Description: Component "component "_" (2546)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly.
View 6 Replies
View Related
Aug 29, 2007
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
View 15 Replies
View Related
Apr 29, 2008
Hello All Experts,
I have created one custom task with PropertyGrid Control and two button on it. I have everything under one class library project.
Problem I am facing is when i load task and clik on Edit I can not see those properties into that GUI and even functionlity of those two buttons (OK and Cancel) not working but I am able to see those properties in default property window.
If I create this GUI as a seperate window application then I am able to see those properties in GUI and buttons also working but in SSIS I am not able to load the task.
After reading on internet about SSIS they suggest to create everything under one project which I did.
Basically I am trying to populate connection managers like Source Connection and Destination Connection when I load this task and there are much more backend functionlity but at first step i m stuck and not able to see those properties in GUI.
Please help and give your input on it. I was following "Increment Task" example given by MSDN.
If you need more info let me know.
Thanks
View 6 Replies
View Related
Dec 28, 2007
Hi,
I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM.
However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg
The same package works fine against a similar test table with 150k rows.
http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table.
Any hints,advice would be appreciated.
View 18 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Nov 12, 2007
Hi Guys,
This is a question to the SSIS development team. I would like to know what are the requirements to implement custom SSIS Control Flow task in C++ . There is a documentation describing the process when implementing a managed task, but no such documentation exists for implementing a task in C++.
Thank you,
Ivan
View 10 Replies
View Related
Jul 6, 2007
HI,
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
Any Comments or sample will help me.
Nitin
View 2 Replies
View Related
Mar 21, 2008
Hi,
I have SQL Server 2005/BIDS installed on a 64-bit server. When I open an SSIS package the properties window for each of the Data Flow tasks is empty. The properties window is there and displays correctly for other types of task but for the Data Flows it only shows the name of the task. To further compound my misery if I try to open a package that dynamically sets a property of a Data Flow task (using an expression) the package fails to open with errors concerning reading the XML of the package.
Packages containing Data Flow tasks still run on this server (both in BIDS and using dtexec) as long as they don't contain expressions that set any Data Flow properties.
Any ideas?
Thanks
View 3 Replies
View Related
Mar 26, 2007
To all the SSIS gurus,
I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.
I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.
Is there a way to do that? Any custom data flow component out there??
Your ideas are greatly appreciated.
Regards,
View 5 Replies
View Related
May 4, 2006
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
View 17 Replies
View Related
Mar 12, 2008
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
View 7 Replies
View Related
Mar 8, 2007
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
View 5 Replies
View Related
Oct 2, 2007
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
A.
View 14 Replies
View Related
Dec 20, 2007
Hi,
I'm trying to get a record count out of a databse using OLE DB Source and row count tasks but keep getting an error. I set up a variable as int32 and select the variable name in the row count task and when I go to the Input Columns tab to select a field to count, it gives me this error:
Error at Data Flow Task[Row Count[505]]: The component "Row Count" (505) has forbidden the requested use of the input column with lineage ID 32.
I don't even know what this means?
thanks,
View 4 Replies
View Related
May 14, 2015
I'm trying to edit the Expressions of a Data Flow task. This seems to happen when I rename some of the Data Flow components but not always. The error I get is:
Element "[ADO Net Source].[SqlCommand]" does not exist in the collection "Properties"
However, if you look at the XML, this property does exist. So I'm not sure why this should occur.
I'm using SSIS 2008 R2 with Visual Studio 2008 V 9.0.30729.4462 QFE.
<component id="1" name="ADO Net Source" componentClassID="{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}" description="Extracts data from a relational database by using a .NET provider." localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="Extracts data from a relational database by using a .NET provider.;
[Code] ....
View 3 Replies
View Related
Sep 18, 2006
Hi,
I'm creating a custom data flow transformation in c#.
I would like to use expressions within this component in the same way as in the derived column component: specifying the expression as a custom property of an output column, then evaluating this expression for each row of the buffer and using this evaluated expression to populate my output column values.
So I've added an custom expression on my output column, and set its expression type to CPET_NOTIFY
IDTSCustomProperty90 exp = col.CustomPropertyCollection.New();
exp.Name = "Expression";
exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
But in the ProcessInput method I don't manage to get the evaluated expressions, when I use exp.Value I get my expression definition and not its evaluation.
Is there a way to get these evaluated expressions ?
Thanks,
Stéphane
View 6 Replies
View Related
May 16, 2008
Hi,
I've created my own custom data flow transformation task (using C#) that
will parse a fullname and output the various name parts. In the
ProvideComponentProperties method, I create 5 output columns (prefix, first, middle,
last, and suffix). In the ProcessInput method, I parse the input and add the
name parts to the buffer. The bad thing is that I€™m making an assumption on
the position of the Full Name input column within the buffer.
I would like the €œuser€? to be able to map their "full name" input column to a known Full Name column so I don€™t have to make any assumptions. This is the first
SSIS task I€™ve tried to create and I haven€™t been able to find very many
examples online.
Any help is greatly appreciated!
Thank you,
Marshall
View 1 Replies
View Related
May 12, 2006
I created a custom transform that has a custom interface and is a wizard that uses a web service. It creates custom properties and output columns on the fly. I set the dialog result to Ok and close at the end of the steps. The transform then has the custom fields and output columns I created in the wizard. I've verified this by right clicking on the transform and going to the advanced editor. If I then immediately run the package, the custom fields don't exist in the CustomPropertiesCollection. If I close the package and reopen it, the properties now are gone. If I then go through the wizard again, thus recreating the properties, they stay and don't disappear. The quickest way to get a working transform is to add it to my data flow then save, close and reopen the package and then go through the wizard. Just saving after I add the transform does not help.
Does anyone know what might be causing this very strange problem?
View 7 Replies
View Related
Feb 23, 2007
Hi,
I created a package with SQL 2005. The package gets the Access DB and then inserts it into SQL Server.
If I open the package in .NET, I can see the SQL Task and Data Flow Task. The SQL Task has a property sqlstatementsource, which has the necxessary SQL code to create the tables.
How can I tell the SQL Task to recompile the SQL code if I give it another DB name, because the tables differ and don't map in the Data Flow Task
Thanks
View 3 Replies
View Related
Mar 29, 2007
I've been having an issue with the integration of a third-party DLL into a custom data flow component.
The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.
I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.
The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".
I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.
What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.
View 9 Replies
View Related
Feb 7, 2007
Hi,
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.
cheers
View 1 Replies
View Related
Jun 27, 2007
Hi all,
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;"
View 11 Replies
View Related
Aug 30, 2006
I've built a simple custom data flow transformation component following the Hands On Lab (http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en) and the Books Online (ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/adc70cc5-f79c-4bb6-8387-f0f2cdfaad11.htm and ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/b694d21f-9919-402d-9192-666c6449b0b7.htm).
All it is supposed to do is create an output column and set its value to the result of calling a web service method (the transformation is synchronous). Everything seems fine, but when I run the data flow task that contains it, it doesn't generate any output. The Visual Studio debugger displays it as yellow, with 1,385 rows going into it, but the data viewer attached to its output is empty. The output metadata looks just like I expect: all of my input columns plus the new column, correctly typed. No validation or run-time warnings or errors are reported.
I'll include the entire C# file below, which only overrrides the ProvideComponentProperties, Validate, PreExecute, ProcessInput, and PostExecute methods of the parent PipelineComponent class.
Since this is effectively a specialization of the DerivedColumn transformation, could I inherit from the class that implements the DC component instead of PipelineComponent? How do I even find out what that class is?
Thanks! Here's the code:
using System;
// using System.Collections.Generic;
// using System.Text;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace CustomComponents
{
[DtsPipelineComponent(DisplayName = "GID", ComponentType = ComponentType.Transform)]
public class GidComponent : PipelineComponent
{
///
/// Column indexes for faster processing.
///
private int[] inputColumnBufferIndex;
private int outputColumnBufferIndex;
///
/// The GID web service.
///
private GID.WS_PDF.PDFProcessService gidService = null;
///
/// Called to initialize/reset the component.
///
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Remove any existing metadata:
base.RemoveAllInputsOutputsAndCustomProperties();
// Create the input and the output:
IDTSInput90 input = this.ComponentMetaData.InputCollection.New();
input.Name = "Input";
IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = "Output";
// The output is synchronous with the input:
output.SynchronousInputID = input.ID;
// Create the GID output column (16-character Unicode string):
IDTSOutputColumn90 outputColumn = output.OutputColumnCollection.New();
outputColumn.Name = "GID";
outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 16, 0, 0, 0);
}
///
/// Only 1 input and 1 output with 1 column is supported.
///
///
public override DTSValidationStatus Validate()
{
bool cancel = false;
DTSValidationStatus status = base.Validate();
if (status == DTSValidationStatus.VS_ISVALID)
{
// The input and output are created above and should be exactly as specified
// (unless someone manually edited the persisted XML):
if (ComponentMetaData.InputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component accepts 1 Input.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component provides 1 Output.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output must be 1 column.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
// And the output column should be a Unicode string:
else if ((ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].DataType != DataType.DT_WSTR) ||
(ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].Length != 16))
{
ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output column data type must be (DT_WSTR, 16).",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISBROKEN;
}
}
return status;
}
///
/// Called before executing, to cache the buffer column indexes.
///
public override void PreExecute()
{
base.PreExecute();
// Get the index of each input column in the buffer:
IDTSInput90 input = ComponentMetaData.InputCollection[0];
inputColumnBufferIndex = new int[input.InputColumnCollection.Count];
for (int col = 0; col < input.InputColumnCollection.Count; col++)
{
inputColumnBufferIndex[col] = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[col].LineageID);
}
// Get the index of the output column in the buffer:
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
outputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, output.OutputColumnCollection[0].LineageID);
// Get the GID web service:
gidService = new GID.WS_PDF.PDFProcessService();
}
///
/// Called to process the buffer:
/// Get a new GID and save it in the output column.
///
///
///
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (! buffer.EndOfRowset)
{
try
{
while (buffer.NextRow())
{
// Set the output column value to a new GID:
buffer.SetString(outputColumnBufferIndex, gidService.getGID());
}
}
catch (System.Exception ex)
{
bool cancel = false;
ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, string.Empty, 0, out cancel);
throw new Exception("Could not process input buffer.");
}
}
}
///
/// Called after executing, to clean up.
///
public override void PostExecute()
{
base.PostExecute();
// Resign from the GID service:
gidService = null;
}
}
}
View 1 Replies
View Related
May 3, 2007
I am trying to read in a flat file, transform the fields and store into a destination database.
In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.
Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?
Linda
View 16 Replies
View Related
May 16, 2008
Hi,
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.
Any help appreciated.
Thanks
Naveen
View 5 Replies
View Related
Dec 4, 2007
Hi
I have a data flow task. If it completes I should update a flag in the database. So How I can I know if the
data flow task has completed or not.
Thanks
Sai
View 4 Replies
View Related
Feb 5, 2007
I have a table which has been loaded from various source feeds. The SourceId relates to the source name and the SourceCompanyId is the sources primary key for the company. I am basically trying to create one row with all the SourceCompanyIds in my column headers. What data flow tasks would be necessary in SSIS?
The structure of the final table is:
CREATE TABLE [dbo].[Company](
[CompanyId] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](75),
[CIK] [varchar](10),
[Ticker] [varchar](10),
[Source1CompanyId] [int] NULL,
[Source2CompanyId] [int] NULL,
[Source3CompanyId] [int] NULL,
[Source4CompanyId] [int] NULL,
[Source5CompanyId] [int] NULL,
[Source6CompanyId] [int] NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
=================================
The table in which contains all the company data
CREATE TABLE [dbo].[SourceCompany](
[SourceId] [int] NOT NULL,
[SourceCompanyId] [varchar](10) ,
[SourceCompanyName] [varchar](75),
[CIK] [varchar](10),
[Ticker] [varchar](10),
CONSTRAINT [PK_SourceCompany] PRIMARY KEY CLUSTERED
(
[SourceId] ASC,
[SourceCompanyId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
View 5 Replies
View Related
Mar 28, 2008
Hi All,
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
thanks
Sanra
View 4 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
