SSIS DATA Flow Task Freezing

May 26, 2008

Hi,

I am experiencing an error where the ssis data flow task would freeze and stop data export from a oledb source to a text file. It doesn't generate any errors the ssis package would just hang. This only happens when I run it in 64 bit mode. When I change the mode to 32 bit the ssis never freezes and runs fine. Has anyone experience this? Is there a fix so I can run my jobs in 64 bit mode?

Thanks,

View 5 Replies


ADVERTISEMENT

SSIS Ate My Data Flow Task!

Sep 7, 2007

I was working all day making changes to my 3MB package. I was adding a large number of transforms that were copied-and-pasted from elsewhere in the same data flow task.

All was going well. I even took the time to have SSIS lay out the task again (1/2 hour). Suddenly I started receiving some strange errors:



After the layout, I noticed two stray components 'way off in the upper right corner. I found that one of them had a duplicate name to a component which had been added hours ago. Even after deleting it, I got "duplicate name" errors.

I copied three components in one selection, and when I tried to paste them, got the error "can't initialize component on paste". I tried them one at a time, but got the same error.

I got errors about COM failures due to marshalling to another thread
I then exited Visual Studio and started it again. To my great surprise, the data flow task I was working on was still there, but was completely empty.

Comparing what I'm left with to my last version in source control, I find that the entire pipeline element is missing from the DTS: ObjectData element!

I'm developing a real love/hate relationship with SSIS. It varies from one day to the next. Guess what kind of day this is!

View 3 Replies View Related

SSIS Data Flow Task Problem

Sep 28, 2006

Hi, there,

I am having some problems with the loading of tab delimited text file (source) to a SQL Server table (destination) using the SSIS data flow task. Package has been executed successfully with no error msg. The number of rows in the text file also matches the number of rows in the SQL table. But, when I check the content of the table, I noticed some of the columns contain NULL which supposed to have value. This happens not to all the rows but only to some rows. I did some testing by removing some rows from the beginning, middle and end of the text file and re-run the package but the result is quite inconsistent. Sometimes, the field got filled, but sometimes, it just contains NULL where it supposed to have value.

What will be the possible causes to this?

Thanks in advance.

Regards,

Yong Hwee

View 2 Replies View Related

SSIS Error With Data Flow Task

Feb 29, 2008

Hi!

When I add a Data Flow Task to a package, save it and then close. When I open it again, I get 3 errors and the detail of the Data Flow Task is empty.

I had try to uninstall and install my SQL Server 2005, but the problem continues.

Can some one help me on this?

Sérgio Cardoso

View 4 Replies View Related

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

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

SSIS Failure - Can Not Add A Row To The Data Flow Task Buffer

Apr 18, 2006

I have a relatively simple SSIS package that I'm building for a data mining process. The package starts with an OLE DB data source, passes the results of a SQL Command (query) along to a conversion step, which then gets sent to a Term Lookup task. The Term Lookup then writes the result to an OLE DB Data Destination. Pretty simple. The OLE DB data source query returns about 80,000 rows if you run it through SQL WB. The SSIS editor shows 9,557 rows make it out of the source, and into the conversion step, 9,557 make it out of the conversion and into the lookup, and about 60,000 rows make it out of the lookup and are written to the results table. Then the package fails with the following errors listed on the progress screen. I was assuming that the 9,557 was some type of batching that was occurring in the process, but now I'm not so sure.

Thoughts?

Frank

[DTS.Pipeline] Error: The ProcessInput method on component "My Component" (117) failed with error code 0xC02090E5. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC02090E5.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[My Data Source Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "My Component" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

View 2 Replies View Related

SSIS Data Flow Task Execution Priority

Apr 13, 2007

I have a package that loads staging tables from an Oracle source DB. In the data flow tab I have 30+ read table/write table task combinations. When I run the package 3-4 of the read/write combos execute at a time. What I'm trying to control is the priority order of the combo execution. My goal is to minimize to total load time by having the larger table transfers run first and the smaller table transfers fill in until they are all complete. Currently, the largest table (16 million) transfers last (because it was the last combo that I created?).



Thanks,

Dave

View 1 Replies View Related

SSIS Sequence Container,data Flow Task

Nov 28, 2005

I am creating a staging database in which I am loading required tables from 2 different sources.
I have 30 different tables to load from source 1 and 10 different tables from source 2.
This is the way I am doing, in Control flow task I am using Sequence container and in that I included the data flow task, the data flow task
has source OLD DB connection from where I select the table and then destination OLE DB connection where I load the data.
So for 30 tables I have one Sequence container with 30 different data flow task and each data flow task has OLE DB source and OLD DB destination.
I wanted to find out if this is the efficient way to do, or if there is any other way to do this.
And for source 2 shall I put in another package or shall I use the same package with different sequence container and follow the same steps
as for Source 1 tables.
Please advice.
Thanks,

View 19 Replies View Related

SQL 2005 SP2 - Cannot Open Data Flow Task In SSIS

May 17, 2007

I have just installed Service Pack 2 on my SQL 2005 Standard Edition.

However, now all my SSIS packages will not allow me to open my Data FLow Tasks. I get the following error:



TITLE: Microsoft Visual Studio

------------------------------

Cannot show the editor for this task.

------------------------------

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)



If I try to create a new Data Flow task I get:



TITLE: Microsoft Visual Studio

------------------------------

Failed to create the task.

------------------------------

ADDITIONAL INFORMATION:

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)



I have tried to install the latest hotfixes after this but they had no effect.

Can anybody help me???? Please?



View 10 Replies View Related

How Can I Append Children To Parents In A SSIS Data Flow Task?

Apr 11, 2007

I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.



In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk



The external agency can only use a delimited file looking like

p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b



where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.



How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.

View 4 Replies View Related

Multi-parameters Issue In Data Flow Task SSIS

Dec 4, 2006

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

View 1 Replies View Related

SSIS - Handling Recursive XML Elements In Data Flow Task

May 22, 2008

Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:


<xs:element minOccurs="0" name="Parameter">

<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />


<xs:element minOccurs="0" name="Parameter">
<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />



<xs:element minOccurs="0" name="Parameter">
<xs:complexType>

<xs: sequence>

<xs:element minOccurs="0" name="ID" type="xs: string" />

<xs:element minOccurs="0" name="Description" type="xs: string" />

<xs:element minOccurs="0" name="Type" type="xs: string" />

</xs: sequence>

</xs:complexType>

</xs:element>


<xs:element minOccurs="0" name="Parameter">
...............

</xs: sequence>

</xs:complexType>

</xs:element>

</xs: sequence>

</xs:complexType>

</xs:element>

But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?

Thanks,
Dhileep

View 1 Replies View Related

SSIS Data Flow Task Slows During Bulk Insert

May 31, 2007

I have an SSIS Package which is designed to import log files. Basically, it loops through a directory, parses text from the log files, and dumps it to the database. The issue I'm having is not with the package reading the files, but when it attempts to write the information to the db. What I'm seeing is that it will hit a file, read 3000 some lines, convert them (using the Data Conversion component), and then "hang" when it tries to write it to the db.



I've run the SQL Server Profiler, and had originally thought that the issue had to do with the collation. I was seeing every char column with the word "collate" next to it. On the other hand, while looking at the Windows performance monitor, I see that the disk queue is maxed at 100% for about a minute after importing just one log file.



I'm not sure if this is due to the size of the db, and having to update a clustered index, or not.



The machine where this is all taking place has 2 arrays- both RAID 10. Each array is 600 GB, and consists of 8 disks. The SSIS package is being executed locally using BIDS.



Your help is appreciated!



View 2 Replies View Related

Lookup Task Data Flow Transformation Causes Data Flow Task To Hang?

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

SSIS Error - Data Flow Task LocaleID 0 Not Installed On This System

May 2, 2006

I get the following error when trying to import (have tried flat files, Excel & Access). I've searched but have not found a resolution. Any help is appreciated.



Jeff

Validating (Error)



Messages

Error 0xc00470b6: Data Flow Task: The LocaleID 0 is not installed on this system.
(SQL Server Import and Export Wizard)


Error 0xc004706b: Data Flow Task: "component "Source - Test1" (1)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)


Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)


Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)


View 33 Replies View Related

Integration Services :: SSIS - Can Use Variables In A Data Flow Task Command

Jul 2, 2010

In my SSIS Data Flow Task, I have a query that retrieves data based on a couple of date parameters. Is there a way we can pass/use the Variables defined in the SSIS package in the query ?

(I am assigning values to those variables from C# code)

The query should look like this:

select ordernumber, customerid from salesorder

where statecode=3 and datefulfilled between @variable1 and @variable2

View 8 Replies View Related

The SSIS Data Flow Task Could Not Be Created. Verify That DTSPipeline.dll Is Available And Registered.

Jul 19, 2007

Hi all,



We are running SQL Server 2005 Standard edition on a Vista Business machine. Whenever we try to use DTS to import or export data we get this message:



The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered.



Of course we have explicitly registered this dll and others.



Also we reinstalled SQL Server (and did it again. And did it again).

And we did the same for Visual Studio Standard (and again).



Any ideas in this elaborate group?



Thanks,

Tjerk

View 5 Replies View Related

SSIS Data Flow Task Fails To Load All Text From Flat File

Jan 2, 2007

Hi Guys,

I
have a flat file which is loaded into the database on a daily basis.
The file contains rows of strings which I load into a table,
specifically to a column of length 8000.

The string has a length of 690, but the format is like 'xxxxxx xx xx..'
and so on, where 'xxxx' represents data. So there are spaces, etc present in the middle.

Previously
I used SQL 2000 DTS to load the files in, and it was just a Column
Transformation with the Col001 from the text file loading straight to
my table column. After the load, if I select len(col) it gives me 750
for all rows.

Once I started to migrate this to SSIS, I
allocated the Control Flow Task and specified the flat file source and
the oledb destination, and gave the output column a type of String and
output column width of 8000. But when I run the data flow task it
copies only 181 or 231 characters out of the 750 required.
I feel it stops where it finds the SPACES and skips the rest.

I
specified row delimiters or CR, and LF. I checked the file under
UltraEdit and there were no special characters in the file that would
cause the problem.

Any suggestions how I can get it to load the full data?

Thanks

View 26 Replies View Related

Integration Services :: How To Achieve Check Point Like Functionality In Data Flow Task Itself In SSIS

Jun 4, 2015

I have huge data and i am loading data from EXCEL to database table, after loading 80 percent data i am getting some error. My package got failed and it has lots of transformation and took around 6 hours to process completely because of that i don't want it to reload from start. if i run it again it should start from next record from where i got the error.

View 3 Replies View Related

Error: The Task With The Name Data Flow Task And The Creation Name DTS.Pipeline.1 Is Not Registered For Use On This Computer

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

Compare Performance (Execute SQL Task Insert And Data Flow Task)

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

Can A Result Set From SQL Script Task Be Used As A Source For Data Flow Task?

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

SSIS Variables Between Data Flow And Control Flow... How To????

May 17, 2007

Hi everyone,

Primary platform is 64 bit cluster.

How to move information allocated in SSIS variables from Data Flow to Control Flow layers??

We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????

Thanks in advance and regards,

View 4 Replies View Related

Error Using Row Count Task In Data Flow Task

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

Recompile SQL Task With Data Flow Task

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

Custom SSIS Control Flow Task Implemented In C++

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

Data Flow Task

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

Which Data Flow Task To Use?

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

Data Flow Task Error To Extract Data From Sql Server To Excel

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

Error At Data Flow Task

Dec 12, 2007

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.

View 3 Replies View Related

Need Help For Design Data Flow Task

Nov 14, 2007

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

View 10 Replies View Related

Data Flow Task Question

May 24, 2007

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?

View 7 Replies View Related

Data Flow Task Error

Jan 7, 2008

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

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved