Is there a transform available which allows you to specify two different tables (same primary key) and compare columns (you identify which column(s) values need to be compared in the transform) between those two tables?
thanks
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
I'm in need of a sql query that I'm not sure is possible. Here is anexample of how it's laid out.employee ID Job class Last Change Date12345 x 2/1/200412345 y 1/15/200412345 z 1/1/2004We know that this person is in job class 'x' because it's the mostrecent change. Is there a way to write a query that will exclude thelines 'y' and 'z' because they are currently incorrect?I would appreciate any help I could get. Thanks
View 1 Replies View RelatedI am importing States into a table and need to change all NULL fields before I perform a lookup. I'm using a derived column to replace the state value. I'd like to find all States that are blank and set it to "--".
Ironically, this works:
State != "" ? State : "--"
but
State == "" ? State : "--"
Does not work
Can someone tell me why?
I am no expert in sql, but I keep stubbling on this problem:I have a table t1 with 2 columns (a,b)I have a table t2 with 2 columns (c,d)I need to delete all records from t1 which have the same value (a,b)than the value of (c,d) in all records in the t2 table.I oracle, this is simple:delete from t1where (a,b) in (select c,d from t2)because Oracle has support for this syntax. Dont remember how they callit. But this is not support in sql server. So I have to resort to:delete from t1where a + '+' + b in ( select c + '+' + d from t2)Of course, a,b,c,d must be varchar for this to work. Basically I fake aunique key for the records. Is there a better way to do this?Thanks
View 3 Replies View RelatedHI, I was wondering if there is a possibility to use a confitional if like this:
IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)
into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.
Thank you,
Ccote
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 data like this in the table :
IntRecpieID strName intMealtypeID Total
100 ‘A’ 1 20
101 'B' 2 30
100 'A' 3 40
Desired Output required:
IntRecpieID StrName 1 2 3
100 'A' 20 Null 40
101 'B' Null 30 Null
Hello,
I understand that it is not possible to use PATINDEX in a Derived Column transform. I'm trying to eliminate leading zeros from a column where the string is always 14 characters long, but the first non zero character could occur any number of characters from the left of the string. The following achieves what I need:
substring([Account No w/0s], patindex('%[^0]%', [Account No w/0s]), 14) AS ExtractedAcctNo
Does anyone happen to know how I might express this differently in a Derived Column transform? Should I consider a calling a function through an OLE DB Command transform on each row instead? Maybe I should have a SQL Task that runs an UPDATE statement against the column.
Thank you for your help!
cdun2
Hi,
Is it possible using derived column transform to change all blank values in a flat file to say a "0"
Basically convert "" to "0"
Thanks for any help,
Slash.
Hi,
I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).
What I have done is:
1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.
2. use an Import Column Transform to read the binary files.
3. use an OLE DB Dest transform to dump the data into my destination table.
I got the error saying:
Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".diagram1.bin" for reading failed. The file was not found.
I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:program filesmicrosoft visual studio 0common7IDE".)
My question is: how to determine the directory path information of the package I am running?
Thanks!
Wenbiao
I recently converted a column that was once an int to an bigint on one of my tables. The modified column provided a generic row id information and there are duplicates within this column. I am trying to perform a self join via the following:
SELECT a.row_id FROM test_db a INNER JOIN test_db b ON b.row_id < a.row_id.
This code use to work when the column was an int but now I am getting high CPU issues since I converted to bigint. I am unsure on why the change to bigint will cause such an issue. The OS/SQL is 64BIT.
Thanks for the help in advance.
For Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.
Many thanks
I want to replace the contents of a value column with itself but rounded to 2 decimal places.
The current column is a double and I have tried to perform this using the following expression but it fails to work.
Code Snippet
Round(cc_vl,2)
How should I achieve this?
i have too many DTS packages to migrate to SSIS, and while examining a DTS package in BIDS (converted with the migration utility) i tried to edit the resulting migrated package, which opened the DTS interface with the two connection icons joined by the big fat arrow with a gear on it...not exactly what i had in mind, iow, it looks like SSIS on the outside, but its still DTS on the inside.
So I stripped out a series of components from a more complex package hoping that simplifying it would reveal the contents of old DTS Transformations tab at least partially set up in a Derived Column transformation.
Can i get there from here, or must i recreate every stinking definition in a derived column manually from the ground up?
thanks very much for your help
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
Thanks for your help.
Hi,
I have a table in sql server 2000.I delete some rows in the table.I would like to know the differences before & after deletion. Is it possible?
We run std 2008 r2. I'm looking at the files this transform is complaining about. They seem to be named appropriately. The customerid folders don't exist when this runs. I'm going to put one in place to see if that is the problem.
The errors i'm getting are...
[Export Column [22]] Error: The file name "c:usersmyuserid heprojectnamecustomeridafilename.doc" is not valid. The file name is a device or contains invalid characters.
[Export Column [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Export Column" (22)" failed because error code 0xC020207F occurred,
and the error row disposition on "input column "FILENAME" (29)" specifies failure on error. An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (22) failed with error code 0xC0209029
while processing input "Export Column Input" (23). The identified component returned an error from the ProcessInput method. The error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
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!
Thank you.
Hi,
Short version:
How would you accomplish comparing ALL the values in two tables wherein the PKs for each table are identical?
Long version:
SQL Server 2000 database with two tables
of identical structure having rows with some new data, some old data.* It's easy to determine which rows are 100% new by
looking for PKs not in the old data.
For the remaining rows, I need to make
certain no rows have been modified.
* Actually, there are hundreds of
pairs of tables as described.
The data comes from flat files, and I'd
rather use the database than Perl in this
case.
I can write code to generate the
list of columns for each table and then
query these as sets. Is there any
other method that might work better?
SQL Server 2000I will to compare a normal table with a replicated audit table havingidentical columns. I wish to report on the differences between thedata.How can I loop though a "column list" in TSQL rather than explicitlynaming each column to compare ?Thanks,Craig
View 11 Replies View RelatedHi,this is easy with OLAP tools, but I need to do it just with MS-SQLserver:fatTableyeartypeval97a197b297c398a498b598c6....yeartype_atype_btype_c971239845699...The problem is number of different types - not just 3 like a,b,c butmore than 100, so I don't want to do it manually likeselectyear, a.val, b.val, c.valfrom(select year, val from factTable where type='a') afull join (select year, val from factTable where type='b') bon a.year = b.yearfull join (select year, val from factTable where type='c') con a.year = c.yearis it possible somehow with DTS or otherwise? I just need to presentthe data in spreadsheet in more readable form, but I cannot find anyway how to export the result from MS-SQLserverOLAPservices to Excel...Martin
View 2 Replies View Related
Hi everybody,
I'm having trouble creating a seemingly simple Comparison report.
I want to be able to create a Table or Matrix that displays the number of items for the Current Year, the Previous Year, and the Difference. I was able to write a script that gives me the count for each item, for each year, as illustrated below:
Item
WhichYear
Count
Apples
Current Year
2
Apples
Previous Year
2
Mangos
Current Year
214
Mangos
Previous Year
204
Oranges
Current Year
13
Oranges
Previous Year
20
Pears
Current Year
19
Pears
Previous Year
50
Strawberries
Current Year
28
Strawberries
Previous Year
40
Ideally, the report Layout look like this, with a column for each year, and a separate column for the difference:
Item
Current
Previous
Difference
Apples
2
2
0
Mangos
214
204
10
Oranges
13
20
-7
Pears
19
50
-31
Strawberries
28
40
-12
Sounds simple enough to me. But when I put it in a Table, I can't get the counts for the Current and Previous Years on one line per item. They end up broken down into two lines (as illustrated in the first chart). When I try to add a grouping, it somehow holds onto the Current Year numbers and ignores the Previous Year numbers. When I put it in a Matrix, I can't seem to write a simple calculation, like finding the Difference between the two columns. Can I add a non-pivot row or column to the matrix?
I know this is a very general question... Any idea on whether I should go for a Table or a Matrix or another approach, like a summary table?
Thank you very much in advance,
- Trevor
Hi guys,
I have a SQL Server 2000 table that contains a Year field and another table containing Products.
The Year table contains only a year field.
The Product table contains ProductId, Date, Quantity.
I'm trying to construct a query such that I can view the Quantity of Products by Year (derived from the Year table).
ie.
If the Year table contains the rows 2003, 2004 and 2005, the resultant query would produce the output: -
PId 2003 2004 2005
50 3 6 7
51 4 2 6
52 8 0 3
Any ideas how I can achieve this?
Cheers in advance,
Rob
I must be missing something somewhere...
I have a simple table with three fields: ID, LastName, FirstName. The ID is defined as the PK. In the table is a record of "12345, Smith, John". The incoming flat file has a record of "12345, Smith, Johnny".
In the SCD transform, the ID is the business key, and Last Name and First Name are defined as historical attributes.
During the load, the SCD transform correctly sends the data down the right path, but the insert fails with a primary key violation - as I would expect since it's trying to create a new current record.
How do I get around this problem without removing the PK ???
thx
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.
The file looks something like this:
FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60
So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I have a requirement to access a lookup table from within an SSIS Transform Script Component
The aim is to eliminate error characters from within the firstname, lastname, address etc. fields by doing a lookup of an ASCII code reference table and making an InStr() type comparison.
I cannot find a way of opening the reference data set from withing the transform.
Regards
Tim
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?