Hi,I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:string myString = myReader.GetValue(0) // this sets myString to first value in a rowIf, however, I change order of columns returned by stored procedure myString would be set to wrong value. Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"];
I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.
Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.
The structure of the data is like this. Code Name AA AA AL Aluminium ALM ALM ALT Aluminium in tonnes AR AR AUD Australian Dollars AUJPY AUJPY CAQ CAQ CC CC CCF CCF CER Carbon Emmission Reduction
The mapping table is like this: XAA AA XAL AL XMA ALM XAL ALT XAR AR
I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?
I am new to using SSIS. I am supposed to move data from a text file to a SQL Server table. I did that successfuly when I simply mapped column one-to-one, but when I could not conditionally map one column to different destination column depending on some criteria.
Example: I want to make SSIS map the column A depending on the value of field X:
If X= Value1 Map A -> B
if X= Value2 map A -> C and so on.
This is an urgent situation. I will really appreciate instant help.
I had to use use ssis 2005 in a short project recently & had littletime to work it out. I was importing a whole bunch of flat files intoSQL Server tables with many derived columns and transformations inbetween.It seems to automatically map columns from the flat file to columns inthe sql table where the names of the columns are equal. But can italso do it automatically on position, so flat file column 1 goes tosql table colum 1, etc, etc? In each flat file I had to manually clickand drag the columns across to map them which took a very long time asthere were hundreds of columns in some tables!Thanks.
As soon as I call the input.GetVirtualInput(); method I get a com exception ,Seem that I am missing a
VirtualInputColumnCollection on the component ,but can't seem to figure out why.
When I drop the all the other components and only keep the OLEDb Source and OLEDB Destination with a flow between them , the call to input.GetVirtualInput() doe not fail with a com exception and I can mapping normally
I have set up a script task in one of my packages that I have set up to modify another package right before running it. This package is nothing more than a data flow task that transfers rows via an sql command from one table into another. The strange thing is I have gotten it to work with some tables but not with others. T
he script bombs out in the loop where i map all of the columns found below, where i use MapInputColumn with the error HRESULT: 0xC0010009 On Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSExternalMetadataColumnCollection90.get_Item(Object Index)
The thing is this happens after looping roughly 55 times but there are still about 100 columns that it needs to loop through still.
Code Block
Dim input As IDTSInput90 = data_destination.InputCollection(0) Dim virtual_input As IDTSVirtualInput90 = input.GetVirtualInput Dim input_column As IDTSInputColumn90 Dim virtual_column As IDTSVirtualInputColumn90
' Iterate through the virtual input column collection and map field names For Each virtual_column In virtual_input.VirtualInputColumnCollection input_column = inst_data_destination.SetUsageType(input.ID, virtual_input, virtual_column.LineageID, DTSUsageType.UT_READONLY)
inst_data_destination.MapInputColumn(input.ID, input_column.ID, input.ExternalMetadataColumnCollection.FindObjectByID(virtual_column.Name).ID) Next
Just for kicks i removed the mapping portion of the code and left in the SetUsageType to see if it would update the available input columns in the destination. The script will then finish successfully but still only the 55 or so fields out of 155 are available in the input. So i then stepped through the script with the mapping portion still disabled and after it loops successfully, i call reinitialize meta data and it produces an error in the input_column variable: HRESULT: 0xC0047041.
I find it odd that this still reports to me that the script finished successfully and I also find it odd that this works fine on two other tables I've tested but not this one. Any insight would be greatly appreciated.
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).
The only way to add a new column to an existing mapping that I know is to go to advanced editor and refresh. This however keeps only the default mapping (where the field names match), the rest is wiped out, so need to restore the mapping manually after that. Risky and annoying at the same time. Is there any alternative?
I have a small problem in parameter mapping for Execute SQL Task. I am using a delete statement with 2 conditions. Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider. I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR. i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype. This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also, I set the property RetainSameConnection = TRUE for oracle connection manager. I am not able to trace this logical error.
The same is working fine in my local machine. But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping? What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and inside the parameter mapping.
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data. Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount 123, Grenadine, 100.00, 75.00, 15.50, 2.00 456, Lime Juice, 120.00, 81.00, 17.25, 2.00
What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.
I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.
The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.
I am all out of creative ideas, any help would be appreciated.
I'm in the process of converting legacy DTS packages to SSIS. I need to populate a table that has more fields than the source file. In DTS I did this with an ActiveX script. How do I go about doing this within SSIS.
In the ActiveX script most of the fields were defaulted with either spaces or zeroes.
One of the Destination fields needs to be incremented by 1 for each new record inserted.
hi,I'm building a multi-lingual website In my database tables I have, in some of them, a column with the Language, because some of the columns depend on what language the user wants to see the site.My question is: what is better? have that column and consequently two row (for two languages) with repeated column information? or have two column within a row with the language specification?e.g. table: id, description, price(1) With language:id,description, price, language='EX' id,description, price, language='EN' vs.(2) id, descriptionEN,descriptionEX,price if I have 500 products in 1 whould result in 1000 entriesin 2 just 500 results can anyone tell me a diference/advantage between the two approachs?thanks in advance.
I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to make it as the format in table B as final. How to convert it? Please help!
We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .
The output for a Select * from ISCMetricslooks like this
What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....
hello all, i am trying to create a view from a table that will keep track of the time between each stage of tasks given. take a look at the data below:
i would like to be able to have just one row per task ID showing the difference between the stages, as shown below . only the 2nd column is a date, the rest are are hours (datediff) between the stage and its preceeding stage :
I am trying to write a query that will return the columns: year/ month, each status type (unknown how many types there are)
Each row is a different join year/ month
Each cell has the count of users that joined in that rows year/ month and currently have the status of the column.
At the moment I have the following query:
SELECT [remortgage-status].status, COUNT(1) AS CountTotal, YEAR([remortgage-log].datetime) AS Year, MONTH([remortgage-log].datetime) AS month FROM [remortgage-status] INNER JOIN [remortgage-log] ON [remortgage-status].clientid = [remortgage-log].clientid WHERE ([remortgage-log].action = N'Joined') GROUP BY [remortgage-status].status, YEAR([remortgage-log].datetime), MONTH([remortgage-log].datetime)
The problem is that each different status is a new row rather than each status being a column.
What do I need to do to correct this? - I dont know all the different possible statuses at this point
I know this is a self join, but I can't remember exactly how it goes.Could someone help me out?create table A{int idA,varchar(30) dataA}create table B{int idB,varchar(30) dataB}create table A_B{int idA references A(idA),int idB references B(idB)}insert into A values(1, "foobar")insert into A values(2, "barfoo")insert into B values(1, "a")insert into B values(2, "b")insert into B values(3, "c")insert into B values(4, "d")insert into B values(5, "e")insert into B values(6, "f")insert into B values(7, "g")insert into B values(8, "h")insert into A_B values (1, 1)insert into A_B values (1, 2)insert into A_B values (1, 3)insert into A_B values (1, 4)desired resultsfoobar a b c dThanks,-- Rick
I have a large amount of data in an XML file. The file is very simple. Off of the root node are category nodes and each category node has many detail nodes. The detail node consist of the node name and the node value. Rather than map the detail to matching named fields in a single record, I would like to send the detail node data to individual data records where the category code, node name and node value are the only fields in the record. For example,
Starting with
<Category>
<Code>001</Code
<Details>
<DetailCode123>75.87</DetailCode123>
<DetailCode728>12.98</DetailCode728>
<DetailCode387>55.72</DetailCode387>
</Details>
</Category>
I want to be able to pick up the 001 code for the category and then transform the above data into 3 separate table records with the following fields (CatCode, DetailCodeName, DetailValue).
001, DetailCode123, 75.84
001, DetailCode728, 12.98
001, DetailCode387, 5572
Does this sort of transform exist? If not, I will have generate my own parsing script. Thought it was worth asking before doing the additional work.
Schedule_S1 contains more than 1 rows with different FROM_DATE and TO_DATE
The query should merge 2 rows into 1 row with 2 different FROM_DATE AND TO_DATE
The following query works fine when there are more than 1 records. When there is only 1 record, it should still return the record (with FROM_DATE_2 and TO_DATE_2 as nulls). Thanks for any help.
SELECT ml1.P_ID, ml1.S_ID, ml1.CURRENT_DAY, ml1.FROM_DATE AS "FROM_DATE_1", ml1.TO_DATE AS "TO_DATE_1", ml2.FROM_DATE AS "FROM_DATE_2", ml2.TO_DATE AS "TO_DATE_2" FROM Schedule_S1 ml1 JOIN Schedule_S1 ml2 ON ml2.FROM_DATE > ml1.FROM_DATE WHERE ml1.P_ID = 59014 AND ml1.S_ID = 25691 AND ml1.CURRENT_DAY = 3 AND ml2.P_ID = 59014 AND ml2.S_ID = 25691 AND ml2.CURRENT_DAY = 3 ORDER BY ml1.FROM_DATE
I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?
I have data in a table. I want the values in the rows to place in columns and columns into rows. Eg:-A table. It consists of three columns and three rows.
How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?
