Hello,
I am trying to do the following:
I have been given an MS Access Database that has a table with columns
I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).
Any suggestions???
I need to write a sql statement to retrieve column headers and rows from 2 different tables.
E.g.:
row1: EMP column list, tag: emp
row2: DEPT column list, tag: Dept
row3: EMP columns data
row4: DEPT columns data
And the data sorted by deptno column
E.g.:
EMP, Deptno, empno, ename, age, ...,
DEPT, Deptno, ename, loc
EMP, 10, 7548, Justin, 43,....
DEPT, 10, marketing, california
EMP, 20, 7345, Carol,26,....
DEPT, 20, finance, New York
EMP, ....
DEPT,...
.....
......
I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
I have excel files where the column headers I care about are on line 5, and the actual data doesn't begin until line 6. Other than deleting the first 4 lines, which is impractical, how can I get the Excel Connection Manager to import the data correctly? I was able to do this under DTS, so I have to imagine it's possible.
Thanks!
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,
[Code]....
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
I created a Stored Proc to compute all the data I need to export to a CSV file.
I use the provider MS OLE DB Provider for SQL Server.
It's a very simple package with a single Data Flow Task.
This flow task is using an OLE DB Source to call a simple SQL Command : Exec MyStoredProc.
There are no params.
This Stored Proc is using table variables to compute the data.
It takes about 10 seconds to return anything.
The problem is that the mapping doesn't work with the OLE DB Source.
There are no fields at all shown in the mapping screen.
I tried to replace the Stored Proc by a version which only returns fields and no data.
Then the mapping would work just fine.
The package is then compiling and working fine.
But everytime I put back the real stored proc, even without changing the SQL Command, the SSIS execution breaks at execution.
It keeps saying :
"Error: 0xC0202005 at Data Flow Task, OLE DB Source [477]: Column "RecordType" cannot be found at the datasource."
My guess is that SSIS doesn't wait the 10 secs and thinks the Stored Proc is not returning anything.
What can I do to make this work ?
Thanks,
Vincent
Hello,
What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?
Is this possible?
Thanks,
awiora
can somebody show an example of how to map source and destination columns when uploading a file to sql server?
Also, please send me the mapping when i want to map source to different destination columns.
Hi,
I am in a bit of a quandry. I have an Excel spreadsheet source, but I only want certain rows from the spreadsheet, which will be delineated by a header and footer row (Start and End). Here is my script thus far (see below).
My question is, what is the correct way to do this? I guess I am stuck on syntax. i.e.) Do I need to create an OUTPUT BUFFER?
Thanks
==============================
Sample data:
blah
Start
Frank 1234 1234 1234
Sue 2345 2345 2345
End
blah
blah
==============================
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static Section As String
'Set sections
If Row.Name = "Start" Then
Section = "Start"
End If
If Row.Name = "End" Then
Section = "End"
End If
If Section = "Start" Then
'GRAB THESE ROWS AND SEND THROUGH THE PIPELINE
'IGNORE ALL OTHER ROWS!
'What syntax goes here?
'Output buffer?
End If
End Sub
Hi,
I have an Excel source, which I have hooked up to a Data Conversion task. I have defined "Output Aliases" for all my columns in the Data Conversion task.
However, when I try to map the columns from the Data Conversion task to the table columns, there is a list of column names, which do not correspond to the names I defined as "Output Aliases."
For example, one of the Output Aliases is "col1." However, when I go to map it, the column name is not "col1" but "My Excel file.col1".
Why is this happening? I have not had this problem before.
Thanks
Learning how to use SSIS...
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
http://msdn2.microsoft.com/en-us/library/ms139904.aspx
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
...cordell...
I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
View 3 Replies View RelatedHi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
The Source Data is
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1---151---- ----- ----- Y----- -----
2---151---- -----Y----- -----Y-----Y
3---152----Y----- ------ ----- ------
4---152---- -----Y------ ----Y------
The Expected Output should be
ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1--151----- ----Y------Y-----Y-----Y
2--152-----Y-----Y------ -----Y-----
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
Is there any way to retrieve the column header name along with your query data?
View 2 Replies View Relatedhello,
Just curious if we can supress column headers in SQL Server..
example:
create table Empmaster
(
empid int identity(1,1),
empname varchar(10),
empsalary numeric
)
insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)
insert Empmaster(empname,empsalary)values('Ram',8000)
Sql:
Select empname, empsalary from Empmaster
would have a result set like (this is not an exact result from analyser)
empname empsalary
----------- ------------
Imran 5000
Raja 5000
Ram 8000
But I was wondering if I could somehow make it display
Imran 5000
Raja 5000
Ram 8000
I actually implementation of this is something vast, but I have illustrated a very simple example to explain what I want to achieve. Might seem a very simple 1 line solution, but I haven't been able to find it. .If ther is any documentation that might help please reply and point me to that..
thank you
:)
I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?
I have a report looking something like this:
Field 1 Field 3 Field 5
RandomInfo1 RandomInfo1 RandomInfo1
RandomInfo2 RandomInfo2 RandomInfo2
Field 2 Field 4 Field 6
RandomInfo1 RandomInfo1 RandomInfo1
RandomInfo2 RandomInfo2 RandomInfo2
It extends along a fair number of pages, and I'm currently using the "# of columns = 3" in the Report properties --> Layout section in order to make it span over 3 columns instead of the usual 1. I'm using a table in which the random info fields are the repeating values, and the Field 1,2,3,4,etc are included in the list control, but not part of the physical table itself.
My question stems from a couple of chunks of data which basically extend over the course of 1.5 pages (yep. There's enough to fill about 4.5 columns worth.)
I currently have
Field 1 RandomInfo50 RandomInfo100
RandomInfo1 RandomInfo51 RandomInfo101
Randominfo2 RandomInfo52 RandomInfo102
etc.... etc.... etc....
-----NewPage----
RandomInfo150 Field 2
Randominfo151 NewRandomInfo1
RandomInfo152 NewRandomInfo2
etc...
What I'm looking to do is have "Field 1" appear at the top of each column whenever a larger chunk of data forces itself into multiple columns, or even multiple columns on multiple pages. I have this nasty feeling it's something silly and blatantly obvious I'm missing...
Any thoughts?
I am trying to import 1 spreadsheet to a database table. I am using SSMS export import wizard to import spreadsheet. My goal is to import whole spreadsheet in one single column.
View 7 Replies View RelatedMy matrix column labels do not appear at all at the lowest level column grouping when viewing in IE7
All is ok when viewing the pdf, or when viewing using the preview tab.
Is this an ie7 bug?
when paging down or across in a matrix based report, it would be nice for headings (row and column) to float (ie not disappear) with the position of the report. Is this possible in RS?
View 10 Replies View Related
There are 2 tables
tPeople
ID Name
1 Arun
2 B
3 C
tAddress
PeopleID Address
1 Test Address A
1 Test Address B
1 Test Address C
2 Address A
3 Address1
3 Address2
Expected Result
ID Name Address1 Address2 Address3
1 Arun TestAdressA Test Address B Test Address C
2 B Address A
3 C Address1 Address2
Address columns repeated = max(count(Address)) for PeopleID
Help me on this!
I can do it through dynamic sql using Execute sp_executesql
Is there any workaround except this?
Hi there,
I'm trying to implement Interactive Soring on the column headers in a matrix in a report. Is it actually possible to do this? I've read several internet posts and stories of implementing the Interactive Sorting in the upper-left corner of the matrix, but this is not what I want, I want to implement it on the column headers .
The goal I'm trying to achieve is to give the user the possibillity to click on a column header to sort the rows below in asc- or descending direction.
Please tell me if this is possible, because all my efforts have not been succesfull! If it's possible, please provide the solution to do this.
If you need more information, please don't hesitate to ask.
Thanks in advance,
Dave Ruijter
BI Consultant
I have a report which runs for last 12 months data. Since this is going to be last 12 months the column headers change every month. How can we implement this with dynamic column headers in the dataset?
View 9 Replies View RelatedHi,Does anyone out there know how to do this? I've been banging head forawhile now trying to answer this seemingly simple question.tia,Mike
View 3 Replies View RelatedI have a report requirement, i am new to reports.
I want to have two lines of column headers.( with 7 columns in parent columns header)
and 7 columns in child column header.
The first column header will show a parent record.
And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.
its a one to many: 1 parent record and below all child records for that parent record.
can i use subreports controls to show all child records of the parent.
Thank you very much for the information.
I am using table object to present report.
table row1 has all the column names and table row2 has the data.
How can i put a horizontal line between column names and the data rows.
Thank you very much.
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
If a single row of data spans a page then the column header is not repeated until the next row on the next page.
Is there a way to overcome this?