Mutiple Table Single Task
Sep 21, 2007
I have about 100 different tables that I would like to bring on nightly basis via integration services. SSIS will process the data and send on to its warehouse destination.
Is it possible to use a single task to bring in all these tables from within SSIS? and can write failed records to flat files at the sametime? instead of defining a data flow for each table and dealing with hundreds of dataflows just have one task that loop through the list of tables.
Wonder how warehouses fed by 100s of tables deal with this kind of scenario?
View 8 Replies
ADVERTISEMENT
Sep 5, 2007
Hello All
Firstly thanks a lot Phil and Jamie on such a helpful article on "Checking to see if a record exists and if so update else insert"
Here is my question
I have about 10 tables and there respective working tables
For examples: A, B, C, D, E.... and WorkA, WorkB, WorkC....
Notes:
1) When I execute a package these work table (Work A, WorkB ...) get populated with certain rows say about 5
2) Its not that all the work table are populated on every execution.
3) Tables A, B, C... have thousands of records in it.
4) Work table is of same structure as there parent table..Like WorkA same structure as A.....
5) The table A and WorkA and as on... are linked with a KeyID
Now I want to build a SSIS package that can
1) Get the the data from these multiple tables(WorkA, WorkB...)
2) Process each row of these tables WorkA, WorkB..
3) Depending upon the KEYID of WorkA., WorkB.. etc Update a Flag colunm of table A, B...where the KeyID is equal to KeyID of Work Table
4) After updating insert that processed row of Work A, WorkB ...into Table A, B..
I can do this if I have one source table and one destination table. Here i have some say 10 randomly source tables to respective random destination .
All I could think of creating 10 different packages as adviced in Jamie's article. But I am sure there might some other alternative.
Can somebody advice me the best practice of doing this. Thanks a lot in advance
View 5 Replies
View Related
Dec 19, 2006
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
View 9 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Jul 22, 2015
I have a table with email addresses and CC_Flag.
Email | CC_Flag
xxxx 0
yyyy 1
zzzz 1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.
View 4 Replies
View Related
Nov 8, 2006
Hi
I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.
The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.
Any help welcome.
View 4 Replies
View Related
Feb 27, 2007
hi frnds,
im very new to SSIS package .my package consists of Single Exceute task.
In Single Exceute SQL task i had 3 seperate queries to get the count of rows of 3 tables.
the Query goes like dis ...
select count(*) AS precheckcount1 from new_main_dts where cust_nbr like '875%'
like dis for another 2 tables i had written with the alias name precheckcount2 and 3
i mapped the variable user::precheckcount1 to precheckcount1 in the resultSet and for other two alias name i did the same.
while executing the package the error is thrown : [Execute SQL Task] Error: An error occurred while assigning a value to variable "precheckcount2": "Unable to find column precheckcount2 in the result set.".
please help me its very urgent
View 3 Replies
View Related
Jul 13, 2006
Hi,
I cannot execute a script task in the VBA code window.
I cannot debug or log if I run a single script task from the right click Execute Task .
Every time I have to run the entire package in order to be able to debug.
What am I missing?
appreciate a help.
Gulden
View 4 Replies
View Related
Nov 6, 2015
I created a simple SQL Task in SSIS.
Connection is ADO.Net
SQL Statement is SELECT 10 AS NumberVar
ResultSet is set to single row
But when executing task I always get error message:
[Task 'SQL ausführen'] Fehler: Die zurückgegebene Anzahl von Ergebnisbindungen ist für 'ResultSetType' ungültig: 'ResultSetType_SingleRow'.
In English something like returned number of bindings is not valid.
View 3 Replies
View Related
Jul 20, 2007
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned). There is no problem to achieve that.
But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. "
I tried to change the type of variable around but with no luck.
Could anyone help with this issue? Thanks!
View 6 Replies
View Related
Jun 16, 2015
I've built an SSIS package in SSDT 2014. The package was running successfully
When I close and open the package it hangs on validation of single task out of all.
OLEDB DESTINATION Task
I have tried several times to close the SSDT and open but facing same issue.
These seems like bugs in SSIS/SSDT. What would cause the relocation of some script code to hang the validation process like this?
View 2 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
View 5 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Jan 3, 2006
Hi there,
I have been at this problem for a few hours now using Net 2.0, and can't seem to find an answer.
I am using a asp:calendar that allows the user to pick multiple dates.
When the user is finished and hits the command button, I would like for those dates to be inserted into a table in my SQL DB called Dates.
The mutildate calendar works fine--, and I have placed all of the sates into an Array. And now have a loop for the array to print them out.
What I want to do, instead of printing, is insert them.
C anyone post some 2.0 code behind for insertion? I have found other "batch insert" code, but when I place in the VB, I get errors telling me it is no longer supported.
For example:
Dim sqlCon As New SqlConnection (no longer works in 2.0) since it doesn't recognize Sqlconnection.
Should I use a SqlDataSource? And if so, how do I prepare the Insert command for it?
Any and all help appreciated!
View 6 Replies
View Related
Feb 22, 2008
Hi Friends,
Is it Possible to combine two different dataset in a single rdl file ?
for example:
emp dept
eno enmae deptno deptno depatname
--------------------------- ----------------------------
1 x 10 10 computer
2 y 20 20 Testing
In RDL File out put will be
Eno Ename Deptname
------------------------------------
1 x computer
2 y Testing
PS : I am not used the join condition.
Thanks & Regards,
G.V.Senthilkumar
+91 9894017253.
View 6 Replies
View Related
Feb 9, 2007
Hi,
I have two tables one called students and location.
I want to delete the location and all records in student corrosponding to that location, can any help or point me in the right direction.
Many Thanks
View 2 Replies
View Related
May 21, 2007
I understand SQL Express has a database size limit of 4G, but I read some forum correspondence here saying the 4G is a per database limitation, and within the Express instance, it can have multiple database of 4G in size.
I create multiple database and got an error saying size limit reached for the primary filegroup. Does that mean the 4G limits apply to the total database size ?
KL
View 1 Replies
View Related
Sep 21, 2007
I have a table like this.
Depositors Table
Value(int) StartDate(Date) AccountID(int)
I want to create a report from this table. the report should look like this.
Value No of Accounts Average Value
For Yesterday
For Last 7days
For Last 30 days
Please Can anyone write a simple query for this?
Thanks
View 3 Replies
View Related
Dec 27, 2004
Hi All,
I'm relatively new to ASP.NET coming from ASP. I've created a new, relatively simple ASP.NET application using WebMatrix with various controls and pages accessing an SQL Server database.
3 people are trying to use this app. After a short amount of time, the server starts kicking back an error message that it can not open a connection. I go in with Enterprise Manager to the SQL Server and I can see a long list of processes there almost as if each page is opening a connection and not closing. I'm explicitly closing all connections in my code where I open them for use by a data reader. What about data grid controls. Do they leave connections open? How about the drag and drop insert/update/delete functions. Unless I'm mistaken, this should close the connection when the function completes, shouldn't it:
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
I originally did this app connecting to an Access database but had the same problem. I figured it was due to the known connection issues with Access via ADO so I upsized to SQL Server and over time, get this same problem.
Any help would be appreciated!
Thanks,
Larry
View 2 Replies
View Related
Aug 23, 2007
Not sure if the title describes my situation or not.
Simplified example is:
I have an [Employee] table with EmpCode, EmpName
I have a second table [NewHires] that has: HireDate, EmpCode, Addedby
Both EmpCode and Addedby contain EmpCode referring to the Employee table.
I wish an output similar to:
New Employee (from EmpCode in NewHire), Hired on (From HireDate), Hired By (from Addedby)
My problem is with an Employee.EmpCode=NewHires.Empcode or Employee.EmpCode=NewHires.Addedby in the Where clause or Join part of the SQL I don't know how to get EmpName from the Employee table twice but using two different EmpCode as the reference.
Thanks in advance - Shawn
View 2 Replies
View Related
Oct 12, 2004
I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.
Example:
Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|
Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|
Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|
Table 4
======|====|
StateID Name
======|====|
How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?
View 7 Replies
View Related
May 20, 2004
I got a server that Crashed. My network group was able to get it up and running but it's vary fragile. One of the disk is done. What's the best way to get the my database:tables,views,stored procedured,dts packages, jobs off the bad server without crashing it again? I used the databae copy utility but found out that some of the database are replicated and it wont allow it to copy.
Thanks
Just another day in the life of a dba
View 2 Replies
View Related
Mar 23, 2007
Hi,very new to SQL queries, and strugling with join conceptI had to do a join based on a single field:select*fromtableA, tableBwheretableA.value = tableB.value(+);this works finebut how can i do the same thing while comparing multiple columns ... iwas thinking something like this: (obviously doesn't work)select*fromtableA, tableBwhere[tableA.value1, tableA.value2] = [tableB.value1,tableB.value2](+);Is there some sort of "tuple" comparison I can do?Thank you.
View 2 Replies
View Related
Jul 20, 2005
using osql to apply SPs in mutiple threadsHello,I got a weird problem when I was using osql to apply scripts for msdedatabase in multiple threads mode. Sometime 2 sps were missing duringthe whole apply process, sometime not, and seems like only those twoSPs met the problem. No error was appeared. Did anyone meet sameproblem before? Or any possible solutions?Thank you very much!
View 4 Replies
View Related
Feb 9, 2008
Hi,
I am using a stored procedure to take backup of my database from the Visual Basic Programming.
Before i posted one of my thread with the same thing, so i was recommended to go through with DMOSQL do deal with SQL server with Visual Basic Programming. For me, this takes some time to understand the complete concept.
Because of urgent i am using stored procedure to take backup with the following:
---------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.BackUPBLMSDB
(
@RP nvarchar(200)
)
AS
declare @backupfilename nvarchar(200)
set @backupfilename=@RP
BACKUP DATABASE [BLMSDB] TO DISK = @backupfilename WITH NOFORMAT, NOINIT, NAME = N'BLMSDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
---------------------------------------------------------------------------------------
Example @RP="D:ackupBackup-1 09-02-2008 11-24"
Every time i am passing a parameter with somename and dateandtime(System).
Example
Backup-1 09-02-2008 11-24 2900KB
Backup-1 09-02-2008 12-30 2900KB
Backup-1 09-02-2008 18-10 5400KB
Backup-1 09-02-2008 22-00 2900KB
Backup-1 09-02-2008 22-00 2900KB
I would like to clear my doubt, is it a good practice to take backup with different names. The above one i store four backups . If the system crashes and i created the new database with the same schema without any data present in the tables, can i restore previous backup database to the newly created database.
Moreover, The first two backups contains "2900KB", the third one is "5400KB" after the data is being modified. Look at the fourth one it is "2900KB". Why the size is being reduced to "2900KB" after taking backup eventhough i didn't delete or added data into the database.
Hope you will solve my problem.
Thanks.
Best Regards,
Kashif Chotu
View 5 Replies
View Related
Sep 29, 2014
i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic
View 5 Replies
View Related
Feb 8, 2006
Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst
View 2 Replies
View Related
May 12, 2006
Is it possible to count the same field with different criteria. It would be something like this.
car_table
car_id
car_name
car_brand
So you would execute a statement which would count(car_brand) with two different criteria.
I am not sure if this is possible or if there is another way to approach it.
View 1 Replies
View Related
Feb 8, 2006
Hi all,
Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?
Code:
SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],
LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],
LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],
LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],
LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],
LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],
RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]
Thanks
Tryst
View 2 Replies
View Related
Jul 29, 2006
hello. I am trying to speed up my asp.net caledar.
I need a Stored Procedure that takes a Parameter.
the Parameter will be a Date.
then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar.
the table looks like this
classid
classname
classdate
classtime
(there are more than one class happening on the same day)
I need the retuen value to look like this
"Yoga 9:00am" & "<p>" & "Jazz 11:00am" & <p>
can someone help me or point me to a sample. this is really hard. thank you
View 6 Replies
View Related
Apr 12, 2007
Is it possible to delete multiple rows from multiple tables based on information specified. Can you write a query that would pull the information if you knew what tables it would need to look in? If anyone know I would greatly appreciate any help I am not sure of this.
Thanks,
Traci
View 1 Replies
View Related
Sep 13, 2007
I need to return row counts for a list of all our users. The problem with the first query is that it doesn’t search for names within the column, it considers a list of email addresses a unique entry. I need to be able to see how many times each email address appears in the database. The second query obviously does that but I don’t want to have to copy and paste 500 usernames. the to_addr_head is a text column if that matters. Thanks!!
SELECT
Table1.to_addr_head,
COUNT(*) AS "COUNT(*)"
FROM Table1
group by Table1.to_addr_head
ORDER BY "COUNT(*)" DESC;
select count(*)
where Table1.to_addr_head like '%username%'
View 15 Replies
View Related
Jun 4, 2007
Hi All,
In the BI development studio when I have to change the data source for data sets within a report, I have to go to each of the datasets individually to do this. Is there a quicker way to do this. Say I want to change data source for the entire report in BI dev studio.
thanks
Sonny
View 3 Replies
View Related
