Hi all, new to SSIS so please bear with me on the noobie question:
Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor.
Need to copy the data to a new database and tables Then for each table: 1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order.
What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rows
I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped. Any light shed would be greatly appreciated. Many thanks in advance, Tamim.
Hi all, I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example Admit = Admit Accept = Admit then Accept Withdraw Accept = Admit, Accept, then Withdraw Decline = Admit then Decline As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits: Admit Accept Withdraw That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns? Thanks for any help or suggestions you have.
I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:
I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:
SELECT A.AccountID, c.CustomerNo, c.Surname, c.Name, c.TaxNum FROM Accounts A left join AccountCustomer ac on ac.AccountID = A.AccountID left join Customers c on c.CustomerNo = ac.CustomerNo order by A.AccountID;
As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:
I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?
I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.
E.g. If I have the following data:
Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald
W2k3 server, SQL 2005. @@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
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 have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
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.
Hi 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
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?
There are multiple Type's for some AcctNbr's and what I want to do is run a query on the database so that if the AcctNbr has multiple Type's and CodesValue's it takes them and creates new columns like so:
Code:
AcctNbr MailCode_90 MailCode_99 SEG1 4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that I need to do this with for each account number. If someone could give me a base code to try I could start somewhere. I am an SQL novice. Thanks.
