Hello, I am a beginner user of SQL 2008 R2 so, I could use some assistance. I have 4 raw data tables created in SSMS. The table names are patients, physician, mislocation and misservice. I update the data in these tables with 4 separate queries, one for each table. The patients table is linked to all three tables. The links are physician mnemonic, mislocation mnemonic, misservice mnemonic which are all contained in the patients table. The primary key in the patients table is patient urn. I like to display the data from all 4 tables in one Excel spreadsheet. My question is would I have to create one fact table and 3 dim tables to do this? If so, should I perform this task in Integration Services and created an SSIS package? Or is this necessary? Brian. Brian Roberts

I should add that all 4 tables are found in one database called TEST.Brian Roberts

If you know what all columns you need in the excel spreadsheet, then you can come up with a sql query (querying all the 4 tables) and use that as the source inside SSIS data flow task. Then use a excel destination component and take all the records from source to excel destination.Nitesh Rai- Please mark the post as answered if it answers your question

I created one query called Admissions that inserts data into all 4 tables. It executes successfully in SSMS as a new query. I then added the query as an SQL command in the Data access mode and it parses and previews without error as an OLE DB Source. However, when I added the same query to the Excel Destination within the SQL command I get the following error: TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [Excel Destination [241]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80040E14 Description: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". I am not sure how to resolve this error. Jigsy50 Brian Roberts

In Oledb source you need to give the select query that will retrieve all the relevant rows from all the 4 tables. Can you tell us what is your source query in SSIS?Nitesh Rai- Please mark the post as answered if it answers your question

I simple combined all 4 queries into one. Here is the source query: Truncate Table patients; Insert patients EXEC [dbo].[ExecuteOpenGateQueryInsertIntoTablesMAGIC] @conString = N'DATA SOURCE=172.16.2.1;USER=cmh;PASSWORD=qwe!234;Directory=LIVE.MIS;DATABASE=ADM.CMO', @sql = N'SELECT TABLE patients ADM.PAT.urn AS [ADM.PAT.URN, 30, String], ADM.PAT.name AS [ADM.PAT.NAME, 30, String], ADM.PAT.bed AS [ADM.PAT.BED, 3, String], ADM.PAT.acct.number AS [ADM.PAT.ACCT.NUMBER, 12, String], ADM.PAT.admit.date AS [ADM.PAT.ADMIT.DATE, 8, String], ADM.PAT.admit.time AS [ADM.PAT.ADMIT.TIME, 4, String], ADM.PAT.service AS [ADM.PAT.SERVICE, 10, String], ADM.PAT.sex AS [ADM.PAT.SEX, 1, String], ADM.PAT.age AS [ADM.PAT.AGE, 7, String], ADM.PAT.admit.doctor AS [ADM.PAT.DOCTOR, 10, String], ADM.PAT.reason.for.visit AS [ADM.PAT.REASON.FOR.VISIT, 50, String], ADM.PAT.location AS [ADM.PAT.LOCATION, 10, String], ADM.PAT.ccdqr.response["ALCDD"] AS [DD, 50, String], ADM.PAT.length.of.stay.so.far AS [ADM.PAT.LOS, 4, String] From [ADM.CMO]ADM.PAT.room.bed.index Join [ADM.CMO]ADM.PAT.main Join [ADM.CMO]ADM.PAT.computed.fields Where ADM.PAT.status = "ADM IN"', @parameters = NULL, @schemaFile = N'C:\Program Files (x86)\BEC\defaultMAGIC.npr' Select * from patients Truncate Table physician; Insert physician EXEC [dbo].[ExecuteOpenGateQueryInsertIntoTablesMAGIC] @conString = N'DATA SOURCE=172.16.2.1;USER=cmh;PASSWORD=qwe!234;Directory=LIVE.MIS;DATABASE=MIS', @sql = N'SELECT TABLE physician MIS.DOC.DICT.mnemonic AS [Phys.Mnemonic, 10, String], MIS.DOC.DICT.name AS [Phys.Name, 30, String] From MIS.DOC.DICT.main Where MIS.DOC.DICT.active = "Y"', @parameters = NULL, @schemaFile = N'C:\Program Files (x86)\BEC\defaultMAGIC.npr' Select * from physician Truncate Table misservice; Insert misservice EXEC [dbo].[ExecuteOpenGateQueryInsertIntoTablesMAGIC] @conString = N'DATA SOURCE=172.16.2.1;USER=cmh;PASSWORD=qwe!234;Directory=LIVE.MIS;DATABASE=MIS', @sql = N'SELECT TABLE misservice MIS.SVC.DICT.mnemonic AS [Location.Mnemonic, 10, String], MIS.SVC.DICT.name AS [Location.Name, 30, String] FROM MIS.SVC.DICT.main', @parameters = NULL, @schemaFile = N'C:\Program Files (x86)\BEC\defaultMAGIC.npr' Select * from misservice Truncate Table mislocation; Insert mislocation EXEC [dbo].[ExecuteOpenGateQueryInsertIntoTablesMAGIC] @conString = N'DATA SOURCE=172.16.2.1;USER=cmh;PASSWORD=qwe!234;Directory=LIVE.MIS;DATABASE=MIS', @sql = N'SELECT TABLE mislocation MIS.LOCN.DICT.mnemonic AS [Location.Mnemonic, 10, String], MIS.LOCN.DICT.name AS [Location.Name, 30, String] FROM MIS.LOCN.DICT.main', @parameters = NULL, @schemaFile = N'C:\Program Files (x86)\BEC\defaultMAGIC.npr' Select * from mislocationBrian Roberts