My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View 5 RepliesHello,
I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.
I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:
- OLE DB source, reading +- 800,000 rows from a SQL server database
- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.
- An aggregation
- OLEDB target, fact table in a SQL server database.
It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.
Any comments and advice will be greatly appreciated.
Thanks.
(PS some machine details:
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:WINDOWS
System Directory C:WINDOWSsystem32
Boot Device DeviceHarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:pagefile.sys)
Hi,
The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.
My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?
Many thanks, i hope that made sense.
Grant
I have implemented a single lookup and would like to know the optimal approach to implement multiple lookups within a single €œdata flow task€? i.e. my question was if I had to look up multiple reference tables to obtain surrogate keys. I am oversimplifying for illustration purposes€¦
Source Table
Destination Table
Customer
Customer
Customer Name
City
State
Name Key
City Key
State Key
ABC
Los Angeles
CA
1
2
1
MNO
Boston
MA
2
1
2
Reference Tables
Customer Name
Customer City
Customer State
PK Key
Customer Name
PK Key
City
PK Key
State
1
ABC
1
Boston
1
CA
2
MNO
2
Los Angeles
2
MA
I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately.
The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible)
Thanks
Hi:
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
MA2005
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
What SSIS Task or process can i use to stop my Control Flow Process from running?
I created a SQL Task to do a count on a table to see if there is data, if the count is > 0 then the Control Flow task must continue, else it uses a RAISERROR statement which i use with the event handler, but i want to put something in the event handler to stop the process then and not continue?
Any help will be greatly appreciated.
Kind Regards
Carel Greaves
I would like to know how I can add the following sample code to my Source data on Data Flow on SSIS, or what other options there are. The main issue is time as we have talking about 100's of millions of rows
select Sample,
CASE
WHEN Sample IS NULL
THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL
THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable
what I have done at this stage is just to Create a SQL task with a Insert into
INSERT INTO [dbo].[TestTable1]
([Sample]
,[Sample_1_6])
select Sample,
CASE WHEN Sample IS NULL =THEN NULL
WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' '
ELSE RTRIM(SUBSTRING(Sample, 1, 6))
END AS [Sample_1_6]
from TestTable
If there is a way adding this to a dataflow so I van use fast load that would really be the best solution. I know there are derived columns, but would this really be faster than the straight insert into in a SQL Task? If this is the way to go what is the code I would use in the derived column or any other option.
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,
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
Code2.....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
thanks in advance
Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!
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 was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
View 2 Replies View RelatedI know I should know the answer to this, but I just can't quite get the syntax down
Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
View 2 Replies View RelatedHi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
Thanks.
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
View 3 Replies View Related
Hi,
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?
Thanks
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned?
Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql)
{
DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString());
SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon);
System.Data.DataSet ds = new System.Data.DataSet();
SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt;
} Here's my stored procedure:
ALTER PROCEDURE dbo.MyStoredProcedure
(
@Page int,
@AmountPerPage int,
@TotalRecords int output
)
AS
WITH MyTable AS
(
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum
From Table
where Deleted <> 1
)
select * from MyTable
WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*)
from Table
where Deleted <> 1
RETURN
Thanks
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
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?