I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.
We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.
If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?
And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.
Hello Friends, I need suggestion/help I have to build a package using SSIS and the requirement is like this, First I have to check the data source type(3 types for now) then I have to select a query for each different source from a table (basically the connection string and query changes for each query) and load the data into the dimension and fact tables.
I am not able to get the full picture of how I am going to achieve this, there is little complication to this one of the 3 sources has 15 servers from where i have to extract data Hope I am clear enough
I have a vb program that calls a sp. My vb program connects to DataSource1, Catalog1 and to DataSource2, Catalog2. My sp resides in Catalog1 using a table from catalog1 and a table from catalog2.
OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component. Each source returns 1 row of data with 1 column each except the Script Component. It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry. I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component? Please if anyone can help that would be wonderful.
I am working on a typical data conversion project where we are migrating data from an old data model to a new data model, using SSIS. Both the DBs are in SQL.
Now we have a situation where say there are 25 source tables and 20 odd target tables.
For transporting data, we are using OLEDB Source & OLEDB Destination transforms. However, each transform maps to one view or one table. As a result, the Data Flow is really messed up with 45+ transforms in it. Is there an elegant way of doing this ? With say just one datasource or maybe fewer transforms?
Information for configuring Service Broker when you have multiple initiators seems to be thin on the ground. The examples are all point-to-point and seem to require separate logins for each initiator and corresponding exchange of certificates using a multi-pass installation. E.g. Rushi's ServiceListing API.
We have an application with several hundred workstations which need to send transaction data to department servers and then on to corporate. There is also a need to replicate small amounts of reference data to all levels.
The examples seem to generate an installation and administrative nightmare.
I have a question - would the following scheme work?
1. All our machines are on the same domain so transport security can be handled by Windows Security (One less set of certificates to exchange).
2. Generate 2 Certificates on the Corporate database and export them to the department and workstations use these pre-installed certificates in each database to handle dialog security for all converations. Ie. multiple workstations feed into one user id on the department server using a single certificate. Ditto for all the department servers feeding into corporate.
3. Use Service broker id's to determine where messages are sent (as the same services would be installed at multiple locations).
4. When a database is intalled/restored at a workstation does this invalidate any certificates that are shipped with it?
Hello, I have a datagridview setup right now to display data from one table in my database. However, I am going to need it to pull certain columns from certain tables and put them all into one datagrid. The three tables are demographics, financial, and procedures. Can someone give me an example on how I'd go about doing this? I'm using vb.net.
The procedures table is a list of options that will be displayed in a combo box, depending on the option selected, other cells in the datagrid will be filled with certain data, this data is in the procedures table as well. All of this data will be alongside a customer id located in the demographics table, and will all be recorded into the financial table.
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?
Hi, I am new to Reporting Servies. I need to create a report that is shared by different offices, each office has it own database. So I will need to include multiple data sources. I want users to select their office first (parameter) and then based on their selection they can see the report with the data source from their office. Can I do it in one report. Or do I have to create multiple version of same report for each office. I would appreciate any help. Regards, Hasn
There are two EXCEL sources one destination table. Each record in the destination should be populated with 2 columns from one source and 2 from other source.
Source 1: ID Name 1 abc
Source 2: Address Location 232/2 xyz
Destination: ID Name Address Location 1 abc 232/2 xyz
I tried using UNION ALL transformation but it fetches 2 seperate records (assuming that one record in each source). How to achieve this without using SCRIPT component?
Is there a best way of writing stored procedure to access data from multiple databases. we have an archive database and a live database. I need to retrieve the data from both and merge together, perform calculations and then display the data.
ASP.NET on SQL ServerI've been asked to quote for developing a system to expose data on aweb application. Most of the data will come from SQL Server DBslocated on a single box. However, some of the data will be sourcedfrom ORACLE which is located on a different box. It may be necessaryto create VIEWS and Stored Procedures joining these DBsDoes anyone have any pointers, clues, hints, tips or pitfalls that Imight consider while making my proposal? What sort of extracontingency should I allow for the connection to ORACLE? Should I doall the data retrieval on the DB server, or should I do it on the Webserver?Any thoughts would be appreciated.ThanksWilliam Balmer
I have a group of reports that are the same for 3 different companies, the difference is the data connection. Is there a way I can change the data connection based on a variable passed in at the time of report execution?
Am having difficulty with report/query parameters, where the report now regularly tells me that I must declare the scalar variable @Site.
I want to use 3 data sources :
1. to select a site from a list of sites - works fine and I can select at runtime
2. Once this is selected - I need to present a pair of dates to the user - min and max for data for the site - needs selection 1 to have been performed. Now I HAVE seen this work - once, the date selectors were greyed out until the site had been chosen, then they became available. Now I get the scalar variable error.
3. Finally I will pull the data with 3 parameters (site, startdate, enddate)
This was almost working, and the detail was produced for item 3, until I introduced the date selection option. Now neither item 2 or 3 will accept the users selected site - its from a drop-down.
All 3 queries are being performed by SQL SP's :
1. exec getsitelist - used to populate the dropdown to select @Site
2. exec getdates @Site - used to preset the start/end dates (NB I would really like calendar control here to select the date, with the value pulled from 1 to set the start point - but hey lets walk first ;-).
3. exec GetData @Site, @Start, @End
SOooo - can SSRS2005 even support dependent parameters of this type ??.
If so - whats the best way to create the parameters etc. ??. NB I can see all 3 parameter defs in 'Report Parameters'.
I am using SQL RS 2005. I have a report that is using multiple data sources from different offices. I can add these data sources in my report but is it possible that I can select a specific data source based on a selected parameter value. In this case user from each office, select their office location and be able to see the data from their office. I would appreciate any suggestions. Sal
I have multiple data sources that I would like to use for a single report. The data sources are separate due to security requirements. I have a table that I can get the Initial Catalog from. How do I get that field from the database into the data source connection string at run time?
I saw this link here but the light bulb didn't go off:
I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables.
I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue. http://www.mssqltips.com/tip.asp?tip=1322 error "The destination component does not have any available inputs for use in creating a path".
I have the following scenario: N identical Databases (corresponding to different Fiscal Years, with names <Company Name>.<YEAR>). We want to consolidate the N DBs to a New Datawarehouse.
In SSIS we have designed a Dataflow that reads through a OLE DB Source (Connected to one of the N Databases) and maps to a OLE DB Destination (Connected to the NEW DB).
The question is, how we loop in SSIS through the N identical Connections, so to repeatedly execute the designed Dataflow, each time with a different Connection?
I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.
As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.
For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:
I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
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
I have a report designed in RB3 that uses a data source from a SQL database that is on the report server. I want to add data to the report from an access database an a network drive.
I can add the second data source and create a data set to add data to the report. The dataset query returns data from the Access database but when I run the report I get the following error.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Feasibility'. (rsError OpeningConnection)
Also when I test the connection to the Access data base I get error ERROR (IM002) (Microsoft)(ODBC Driver Manger) Data source name not found and no default driver specified.
I noticed that Report Builder is connecting to the report server. If I disconnect from the report server I can a can connect to the Access data base but not the SQL database.
How can I get the report to run against both data sources?
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.
An error occurred while executing a command. Message: Invalid object name 'Table2'. Command: SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model FROM Table2) t
Is there any way to create a report with multiple data sources?
I am writing an insert stamenet that appears like:
INSERT INTO SppTarget (IndicatorNumber, Part, Years, Target, CompareMethod) SELECT '8', 'B', '20052006', '0.682', '1' UNION ALL SELECT '8', 'B', '20062007', '0.688', '1' UNION ALL SELECT '8', 'B', '20072008', '0.692', '1'
What if I want to SET Target = NULL in this statement, how can i do that?
I have set up a MSX server for managing all of our backup jobs. However when I try and create a DB maintenence plan I can only see the system DBs not user created ones!
Can I do an insert with one column value being specified and the others being retrieved? Basically, I have to take an input from the user, join that input with the result of a select (based on that input) and post that to a table I don’t suppose we can do something like
Hi i have data on a Server in a different database which i like to access from within my ssis job.
I just need to look up information from one table on this database so i can references it. Is there a way of doing this is SSIS. Rather then me having to load the data from one database to another as the data may change.
i tried having 2 sources of data feed into a look up but that does not work..
On my home page I have several different folders to reports which require different data sources. the problem is that within these folders there are multiple copies of the same datasource. is it possible to store all of the datasources in one folder, one location? it would certainly be easier when changes to usernames and passwords need to be modified!
I have around 120 tables. I am using script component to pull the values from oracle stored procedures. I do not want to create 120 source & desitinations in my dataflow. Please advice how can this be possible with one script component and one oledb destination.
I noticed I can add mutiple outputs in 1 script component which makes the script component to work like a dataset (container of different recordsets (tables) ), if I am correct. Can this be redirected to an dynamic oledb connection.
I searched and read about Data Sources and I'm seeing that there is no advantage in using it, which is what I found from playing around with it.
I expected that you would set a global connection in Data Sources and somehow link this to the things in your Connection Manager, giving you one place to switch from one environment to another. But reading the discussions here and playing around with it, this is not the case.
So, why is it there?
Next question.... another thing I gathered so far is something called "Configurations" that will do what I was describing above. Where do I do this?
