I am SSIS newbie and need help in desigining this flow.
Source Sqlserver2005
Select records based on complex sql statement from a [InstanceA].[DatabaseA].[TableA]
Target Sqlserver2005
Insert the records into another table [InstanceB].[DatabaseB].[TableB] only if these records are not present
Take the records from [InstanceB].[DatabaseB].[TableB] and insert it into
[InstanceB].[DatabaseB].[TableC] only those records which are not in C
And finally
Take the records from [InstanceB].[DatabaseB].[TableB] Join them with
[InstanceB].[DatabaseB].[TableC] and insert it into
[InstanceB].[DatabaseB].[TableD] only those records which are not already in D
Can somebody please help me in visualising this solution .
I am having problems populating a target and then using that populated
target as a source for subsequent targets.
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated. Thanks, Ricardo
I am trying to build something similar to www.alienware.com where it lets you build your own computer. I was wondering if some one could help me design sturcture to do it on my own. I am zero in DB and know little asp. I am trying to do it for my own site.
How can I design reports for Reporting Services without using Visual Studio.NET? I have an SQL Server 2000 with RS but I dont have a tool for designing reports. Thx for help
Friends, Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
Dear Friends,I'm a junior DBA, I've to prepare an online examination. for this, I've three categories. a)beginer level b)intermediate level c)expert level
again here subjects are 6. like sqlserver,oracle,c#,vb.net,html,javascript. in these subjects, i've to select these three types of questions. now how can i design for this requirement? shall i create three tables for beginer, intermediate,expert or shall i create 6 tables and write according that?
I am new to sql server. Currently i am using sql server 2000. i have created two tables course and student , with course_id and stu_id , as primary key of the respective tables. Now i am trying to relate them through the diagram , in order to create a relationship between the two. When i try to draw a relationship between stu_id and course_id , i get the following error error: Unable to create relationship 'FK_stud_course'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_stud_course'. The conflict occurred in database 'student', table 'course', column 'course_id'.
Im creating a sample database for purposes of keeping track of employee's jobs + billing codes for that job. currently they just use an excel spreadsheet to keep track of billing.
Question 1:
What would be the easiest way for the manager to enter billing codes? Billing codes are numbers like 956, 958, 805 and they co-relate to prices for each billing code.
1. I want the manager to enter the billing code and have the sql database find out how much that code is and add them up daily. This would prevent the manager from having to input prices and billing codes, and extra step.
Any links on the above topic would be helpful. I'm not looking for anyone to spoon feed me code just point me in the right direction. Im relatively new to sql having only a class of sql @ ITT.
Heres the way I have the db tables laid out currently.
Hi All,I have a table below and I want to design a query to pull all themembers from the TABLE into a Query Result and into a single column withpoints assigned appropriately, but I am having a lot of difficultiesdoing this. Any help is greatly appreciated.TABLEMEMBER1 MEMBER2 POINTSJoe Don 2Macy 1Jack Nick 2Joe Rob 2This is a result I would like to generate from the queryQuery ResultMEMBERS Total PointsJoe 2Don 1Macy 1Jack 1Nick 1Rob 1Thank you,Jim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I need help with database design. I am doing a bowling league program. 1. each bowling center has 1 or more leagues 2. each league has 2 or more teams 3. each team has 2 or more bowlers 4. each week each team bowls 3 games 5. summer leagues last ~13 weeks winter leagues last ~36 weeks
Tables I have + is primary key FK is foreign key BowlingCenter + CenterID ......
League + LeagueID FK CenterID .......
Team + TeamID FK LeagueID
Bowler + BowlerID FK TeamID
Here I am lost. How do I do the 3 games a week for 13 or 36 weeks AND associate the 3 games each week with each bowler?
Hi, not sure if this is the right forum for this question. I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know.... Nick
Hey,I have a fairly large table for my keeping my information about users. My question is, would it be better to separate it, for example, create another table that has all the personal information (like city, street, etc)and have it related to my other table where more of the data-ish information is kept? Thanks,Sixten
Hi all, Does anyone know how I can design the database schema. I mean what tools can be used to the design the database and view the table relationships, etc. TIA.
One interviewer has asked me the following question: What are the things that you consider while designing database? I have told about integrity constraints, and normal forms. but he has added 15 more concepts like 1. indexers 2. Table columns 3. Table rows 4. search facilities 6....... Can any one give full Idea on this question? Thanking you Ashok kumar.
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
With my next application, I'm thinking about establishing a new security paradigm for my programs, with respect to SQL Server.
In all my previous applications, connections are established using SQL Server authentication. So, all my users may log-in under a single log-in, mapping to a single user in a database. My application then has to use its own security arrangement to determine who has access to what forms within the application. There are three major problems with this design: 1) the user name and password to connect to the SQL server have to be resident as strings somewhere in the application code (or ancillary files), 2) everyone connecting to the SQL Server "looks" the same, and 3) you have to give the broadest rights to everyone with the same login and then pare those rights down within the application itself.
By implementing multiple SQL Server Authentication logins I can mitigate problems 2) and 3), but the only way to eliminate problem 1) is to move to Windows Authentication.
Windows Authentication would allow me to resolve all three design constraints, but there is one problem that I see coming as a result.
If I use Windows Authentication, each user must have an independent login to the SQL Server. If I have an application that may have 4,000 to 6,000 users, does that means I have to have 4,000 to 6,000 logins set up on SQL Server?
Is that true?
I could, of course, generate a script to build all 4,000 to 6,000 users, but I am concerned about this.
Is this a "normal" arrangement that SQL Server has no problem-with?
I'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.
Any suggestions to consider? I'd appreciate any opinions.
Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points: 1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.
2. I am using an identity column (INT)to create a unique value within the table
3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)
1.update on this derived table daily (how/what should I do - with triggers/jobs against the original tables?) 2.create a view that would show: - by years the percentage of sold products - quarterly percentage of new customers / rebuyers / etc. - ... 3. Thinking of making a cube in BIDS (with dimensions / measures)
I want to design a login named "JobAdmin" who can add/modify/delete any SQL Agent Jobs but can't access my "db1" database. The jobs would execute some stored procedures in db1, and it could be a user "db1dbo" with db_owner role in db1 to execute the stored procedures.
How could I let JobAdmin impersonate db1dbo to run these jobs? Thanks in advance.
i have created a report which contains a table(header,group header , details)... when i get a copy in pdf format i got more than 20 pages...the issue is the table top border is not displayed in all pages except first page...
for example
if i have two pages ..
first page top border is always displayed properly ..second page top border is not displayed properly.. i think this issue because of alignment , i have tried a lot of scenarios nothing works ...
I am asking something very basic and theoretical, here.
Are there any design tools available in the market for designing SSIS packages? By "design tools" I mean tools which enable us to "plan" or "design" the architecture of a SSIS Solution that will be implemented later, using SSIS, of course.
We have several design tools available for designing a Web Application solution, for example. Similarly do we have something for SSIS?
Are there any design approaches, best practices and/or design techniques published for designing a SSIS solution?
Please note that I am not talking about the SSIS Designer or the BIDS. I am talking about a tool/approach for designing the SSIS solution which can be delivered as a project artifact before the actual coding phase starts?
Well, I have tried to express myself as best as I could. If someone can help me with this, it will be really great!
We are trying to create an app where we pass dynamically a dataset (from our form in C#) to our {report}.rdl file.
We are having doubts about something.
How can we design the actual report if we don't have the datasource until runtime? The reason to do this is due to our complex calculations of data which becomes almost impossible to achieve in a T-SQL environment.
I know with a lot of patience and time (we don't have both) we can achieve it, but even though, we would need to process some data in the client side.
So, the question is... is it possible to design a report without a Dataset? I know we can drag the controls on the layout window, but we won't be able to test it. IS there any workaroung about this?
I have been advised to design multidimensional model based on the operation data model. What I have is ---- Couple of documents about the application. Data model of operational data. Now I have been told to create a datawarehouse from which any type of wuestion can be answered. This seems horrendous. Please guide.
I have created an application that I intended to be 3-tier, but I am not sure if I did it properly. I constructed it like this: I created a DLL that contains methods that validate the passed parameters, checks the data against business rules, and issues ADO.NET methods to access the data. The ASP.NET presentation layer uses Object Data Sources to link to these methods. With this architecture I consider the ASP.NET pages to be the presentation layer, the DLL to be the business layer, and the database itself to be the data layer. Now I am wondering if the standard practice is to have a further division? In this case, there would be a business layer DLL whose only purpose is to validate the parameters passed to it by the presentation layer, and to do business rules checking. There would also be a data layer DLL whose purpose is to accept parameters from the business layer and issue ADO.NET methods to access the database. In this scenario the database itself would be considered part of the data layer, or not considered to be a layer at all. Either one will work, but I would like to implement the architecture that is most accepted, and allows the easiest maintenance. What is the best practice for designing a 3-tier architecture?
