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 RepliesI 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 View Related
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.
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
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.
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
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 ?
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
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,
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?
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.
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.
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,
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
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
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?
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
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.
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
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;"
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;
}
}
}
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
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
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]
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
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
I have a table that holds in each record an image (varbinary(max) actually), a text reference for the image and a MIME type for the image. I need to read this table and for each record that has been created since the last run, I need to create a file with the image as the content, the mime type as the file extension and the text reference as the file name. There will be one file created per record found by the data flow source.
I was assuming that I could use the flat file destination and manipulate the file naming using the contents of each record in the flow but am completely stumped on how to achieve this.
Does anyone have any ideas?
thanks
Hi all, I am getting the following when trying to import text coloums from execl to SQL server 2005. Any ideas?
Error at Data Flow Task [Destination] Coloums "blar" and "Blar_name" cannot convert between unicode and non-unicode sting types.
Any help would be great.
Thanks
Dave
Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.
Hi frns,
I am new to SSIS. I need some help in designing the below dataflow task.
-- Teacher creates several tasks and each task is assigned to multiple students
-- The teacher table contains contains all the tasks created a every teacher
use ods
go
create table teacher
(
yr int,
tid int,
tname varchar(20),
taskid int
)
insert into teacher values(2007,101,'suraj','task1')
insert into teacher values(2007,101,'suraj','task2')
insert into teacher values(2007,102,'bharat','task3')
insert into teacher values(2007,103,'paul','task4')
insert into teacher values(2007,103,'paul','task5')
insert into teacher values(2007,103,'paul','task6')
-- Teacher "suraj" has created 2 tasks
-- Teacher "bharat" has created 1 task
select * from ods..teacher
yr tid tname taskid
============================
2007 101 suraj 1111
2007 101 suraj 1122
2007 102 bharat 2222
-- Students table contains studentid(sid),teacherid(i,e tid ) & taskid
drop table students
create table students
(
yr int,
sid varchar(10),
tid int,
taskid varchar(10)
)
truncate table students
insert into students values(2007,'stud1',101,'task1')
insert into students values(2007,'stud1',101,'task2')
insert into students values(2007,'stud2',101,'task1')
insert into students values(2007,'stud2',101,'task2')
--Note : stud1,stud2 comes under teacher with tid "101"
insert into students values(2007,'stud3',102,'task3')
-- Note : stud3 and stud4 comes under teacher with tid "102"
insert into students values(2007,'stud4',103,'task4')
insert into students values(2007,'stud4',103,'task5')
insert into students values(2007,'stud4',103,'task6')
insert into students values(2007,'stud5',103,'task4')
select * from students
yr sid tid taskid
----------------------------
2007 stud1 101 task1
2007 stud1 101 task2
2007 stud2 101 task1
2007 stud2 101 task2
2007 stud3 102 task3
2007 stud4 103 task4
2007 stud4 103 task5
2007 stud4 103 task6
2007 stud5 103 task4
Now in my target table i need to load the data in a such a way that
use targetdb
go
drop table trg
go
create table trg
(
yr int, -- data should load from teacher.yr
tid int,
taskid int(20),
cnt int
)
Mapping in target column and value to be loaded
==================================================
yr -- teacher.yr
tid -- teacher.id
taskid -- this need to start a new sequence of numbers starting from 1 for each teacher and dont want the task id to be copied as it is.
cntofstudents -- need to count no of students from "students" table for a given teacher and for his assignment
For example for teacherid "101" and taskid "task1" there are 2 students
again for the same teacher "101" and taskid "task2" there are 2 students
For teacher "102" and taskid "task3" there is only 1 student
Similary for teacher "103"
Relation
========
Teacher table | Students Table
yr | yr
tid | tid
After i run the ETL the data should look as follows :
insert into trg values(2007,101,1,2)
insert into trg values(2007,101,2,2)
insert into trg values(2007,102,1,1)
insert into trg values(2007,103,1,2) -- task4 is created by teacher "103" and assigned to 2 students stud4 and stud5
insert into trg values(2007,103,2,1) -- task5 is created by teacher "103" and assigned to 1 student i.e stud4
insert into trg values(2007,103,3,1) -- task6 is created by teacher "103" and assigned to 1 student i.e stud5
Note : If u observer the values in 3rd column of the trg table, instead of directly mapping the taskid we need to generate a separate sequence for every teacher.
BottomLine : for each and every task created by each teacher there should be a unique record along with the count of students in "STUDENTS" table
Can anyone help me out in designing the Data Flow task for this Functionality.
Thanks,
Manu
Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions:
1)
There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter?
2)
In the OLE DB DataSource Component I have used SQL Command(it's simplified):
declare @TmpTable TABLE (WorkCode int not null);
INSERT INTO @TmpTable (WorkCode)
select WorkCode
from Table1
SELECT WorkCode
FROM @TmpTable
SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?
Hi,
I have SQL Server 2005 Express edition on my machine. On an SSIS project in BIDS, when i drag a "Data Flow Task" to the package it returns the following error:
The designer could not be initialized. (Microsoft.DataTransformationServices.Design)
Does this has anything to do with the fact that i don't have SSIS installed on my machine?
I thought that SSIS was only needed (on my machine) for the runtime, just to run the packages. To create and edit the pachages i need to install SSIS on my machine too? this doesn't makes sense, maybe it's another problem.
Can anyone help me on this?
Thank you,
Rafael Augusto
I am having problems with the Data Flow task. It does not even show up in the list of items to drop into the SSIS project.
If I go to the Data Flow tab and hit create, I get the follow error. I have tried repairing and reinstalling, but nothing seems to clear up the error. Without rebuilding my machine, is there anyone who knows how to get the Data Flow Task reinstalled properly?
Thanks
Wayne
TITLE: Microsoft Visual Studio------------------------------Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer. ------------------------------ADDITIONAL INFORMATION:TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskHostNotInstalled&LinkId=20476------------------------------BUTTONS:OK------------------------------
I have a simple data flow task setup...
2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database.
2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table.
I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database)
When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input.
This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination.
Any help is appreciated.
thanks