Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.
[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.
thanks
Hi,
I have to extract data from 3 different tables and insert it into another table. I can use a regular union all query or use 3 ole db sources and then use union all transform. What would be the difference here, is there any beneffit on using the transformation? Why would I use the union all transform instead of a regular query with union all statement?
Thanks
I have an SSIS package I've been working on that has been working just fine until now.
There are four inputs to this package, all OLEDB Inputs based on SQL Statement expressions.
These four inputs are then merged with a Union All step.
One of the merged columns is called "unit" and now suddenly this column cannot be added to the union all step for one of the inputs.
I get the following error:
Error at Import mat [Union All [330]]: The metadata for "input column "unit" (8979)" does not match the metadata for the associated output column.
Error at Import Mat [Union All [330]]: Failed to set property "OutputColumnLineageID" on "input column "unit" (8979)".
My question is
Can anyone suggest how I can diagnose this issue? I can't see the metadata in question, but when I physically inspect the results of the source queries, they all seem consistant with each other. The unit column is derived in similar fashion in each query. I'm a bit lost with this. I can't seem to shake off the error. Nothing has changed in the database to cause this.
Cheers.
I need help on how to find the Row Number Transformation,
I have followed this link http://www.sqlis.com/default.aspx?93
but I cannot find the Trash Destination. Could someone please tell me where I can download it?
Thanks
For detecting delta records, I'm a big fan of SQLIS' checksum transform. I'm having difficulty in it's install on my current machine, however. After the installation and the new transform is added to my DataFlow toolbox... I can't open the UI for the transform to define the checksum. Instead, I get the following error:
===================================
Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft Visual Studio)
------------------------------
Program Location:
at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections)
at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.StartComponentUI(Boolean startGenericUI)
Anyone have any suggestions? Thanks in advance.
Hi i have sql statement like this :
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_a
UNION
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john
2 maggy
1 lee
2 mary
3 louis
How do i make the rec_num continue for the next statement after union.
I would like to get the top 5000 records from a union. The oringinal query is
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
I thought that the I could do:
SELECT TOP 5000 * FROM
(
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}
This gives me a syntax error that I cannot figure out.
Any ideas?
I have 2 tables
tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar
tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int
I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)
so the output would be
viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate
PLEASE HELP i just can not figure it out
thanks...
Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.
Existing procedure:
ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)
select @Cond=''
if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)
if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow
exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int
select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl
set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;
SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')
RETURN
can anybody help me pls. ITs very urgent.
Thanks in advance.
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark
View 1 Replies View RelatedHi there,
This seems a bug to me. Or does anyone has a logical explanation that escapes me?
When in SSIS Designer Version 9.00.1399.00 I add output columns (numeric 4,0 ) to a scriptcomponent and fill them with valid numeric data in thescript I get a database error 'invalid number' when I use these columns in an OLE db Command-transformation . This errormessage disappears when I replaces those columns by a dataconversion to the datatype they originally have.
Derived Column Name Derived Column Expression
STATUS_DEF Replace 'STATUS_DEF' (DT_NUMERIC,4,0)STATUS_DEF
Maybethis info is usefull for somebody else who can't figure out wathever he's doing wrong.
Paul Baudouin
Hi,
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
Please help me
thanks in advance
Srinivas
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
hi!
What do you think is the error in this sql squery?
What I'm trying to do is to get all the records having dates from 2007/01/21 to 2007/02/20 where
YearFr =2007 MonthFr =01 DayFrom =21 YearTo =2007 MonthTo =02 DayTo =20
By the way, this is part of my code: '||||| Open connection
MyConn.Open()
'||||| Create Command Object for filtering time ins and time outs base from Year and Month
Dim odbcCommand_date As OdbcCommand = New OdbcCommand("Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day > ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day UNION Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day < ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day", MyConn)
'||||| Add Parameters and set values.
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
The error is in the UNION part of the query...
PLs help me.
Thanks!
Sheila
Error: The text data type cannot be selected as DISTINCT because it is not comparable.
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp,
a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate,b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND
(a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2',
'yyyVP1', 'yyyVP2', 'yyyVP3'))
union
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp, a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate, b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND (a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3'))
Error: The text data type cannot be selected as DISTINCT because it is not comparable.
I tried union all it returns duplicates records.
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp,
a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate,b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND
(a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2',
'yyyVP1', 'yyyVP2', 'yyyVP3'))
union
SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp, a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate, b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND (a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3'))
I tried transforming data from one server to another using DTS. and then i got an error as below,
-----------------------------------------------------------------------------
Details: Error Source: Pump Data Step
Details: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Facility: 4, Severity 1,Code: 1176, HRESULT:0x80040498
Desc: The Data Transformation Services cannot copy or transform data from a Desktop or server to a standard, Enterprise, or small business server version of SQL server unless your destination server is per user licensing mode.
Source : Microsoft Data Transformation Services(DTS) Package
Source : Microsoft Data Transformation Services(DTS) Package
Code: 0x80040428
Description: Package failed because step'Pump data step' failed.
Error Message: IDispatch error #552
--------------------------------------------------------------------------
This is the full description of the error dialog...
Please suggest me some solutions....
Thanks in advance,
Kamalesh D
Hello all,
I am getting an error that I am stuck on. I dragged the Audit Transformation for error handling (Flat File Source); however, the Audit Transformation is returning an error for one of the standard column "User name". I tried to change the length or take out this column, but the error does not go away. Below is the error message. Your help is much appreciated!
-Lawrence
"The length for "output column "User name" (195)" is not valid for the specified lineage item."
"component "Audit" (180)" failed validation and returned validation status "VS_ISBROKEN".
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
Hello,
I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error
"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"
Why do i get this, what can i do to overcome this?
Thank you
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Union All" (1100) failed the pre-execute phase and returned error code 0x80070057.
I have a flat file source, OLE DB Source, and a Script component, all with output columns defined and loaded. The flat file source has a connection manager defined, as does the OLE DB Source. The Script is just a counter.
I'd like to put the value from each, into a row, then into an OLE DB Destination table. It seems harder than it appears.
Thanks for the look!
I am developing a custom destination component and I have encountered a few areas where there seems to be a lack of helpful documentation and examples.
1. I have not been able to find any information on or examples of creating custom destinations with an error output. The OLE DB Destination has an error output so I investigated the input and error output properties in the advanced editor and found that the OLE DB Destination error output is synchronous with the input (its SynchronousInputID matches the input's ID) and has its ExclusionGroup value set to 1. Using this information, I modeled my error output after the OLE DB Destination.
ProvideComponentProperties:
AddErrorOutput(ERROR_OUTPUT_NAME, input.ID, 1);
ProcessInput:
int errorOutputID = -1;
int errorOutputIndex = -1;
GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);
...
buffer.DirectErrorRow(errorOutputID, 0, errorOutputIndex);
Checking the input and error output properties in the advanced editor for my custom destination component I find the following:
Input
-----
ID: 3515
Error Output
------------
ExclusionGroup: 1
ID: 3516
IsErrorOut: True
SynchronousInputID: 3515
Shortly after I start my SSIS package and it encounters an error row, I get the following exception:
[My Destination Adapter 1 [3512]] Error: System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer90.DirectErrorRow(Int32 hRow, Int32 lOutputID, Int32 lErrorCode, Int32 lErrorColumn) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.DirectErrorRow(Int32 outputID, Int32 errorCode, Int32 errorColumn) at MyDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
2. My custom destination component is used for writing a file with a fixed schema. I followed the means by which source component examples add their output columns, but applied this to my external metadata columns. In my Validate() I check if the ExternalMetadataColumnCollection.Count == 0 and return DTSValidationStatus.VS_NEEDSNEWMETADATA; to force a call to ReinitializeMetaData(). In ReinitializeMetaData() I call a method that creates the input's external metadata columns that reflect my external data source.
This works fine except every time I add my custom destination component to a SSIS Package and go to edit the component I am greeted with a dialog box that states: "The component is not in a valid state. ... Do you want the component to fix these errors automatically?" Pressing the Yes button, I assume, makes the call to ReinitializeMetaData() and I have my external metadata columns. Where is the correct place to add the external metadata columns so the user does not have to take this extra step every time they add my component to their package?
Can i call the FUNCTION within another FUNCTION
Like SUBSTRING(CHECK_NO,2,LEN(CHECK_NO) - 1) ???
I am reading the Check_No "1234321" from the flat file. The file holds all the value within double quote and values are sepearated by comma.
Objective: I am trying to elimiate the double quote using "Dervied Column'.
Strange: The above FUNCTION is working fine while construct the SQL Query.
Pls help me. Thank you.
below is the error message:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.
"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.
Hi all€”Given a date field called [Reading Date] and a time field called [Reading Time], I am attempting to use the following transformation on the field to test for nulls and combine the data into a single field called [Reading Date/Time]:
(DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8))
I get the following error:
Error at Data Flow Task [Derived Column1[177]]: Attempt to parse the expression "(DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed or might be missing part of a required element such as a parenthesis.
Here is a sample [Reading Date]:
05/07/08
Here is a sample [Reading Time]:
19:45:48
I need to be able to handle exceptions if one or both contain NULL or no data at all. Any suggestions on how to fix this?
Thanks,
Jon
when i try to run this following query, i get an error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The ntext data type cannot be selected as DISTINCT because it is not comparable.
/resultados_termo.asp, line 176
the query:
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade
like '%2004%' or ano like '%2004%' )
when i use UNION ALL, i get repeated rows. i need the select distinct on it.
please, help.
thanks in advance.
I’m receiving the following message when attempting to run the SQL statement below.
Error report:
SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2"
Failed: Warning: execution completed with warning
-----------------------
CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS
SELECT DISTINCT code, name
FROM
(
SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6
FROM UIP_SEGMENT_VALUES
WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v)
UNION
SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL)
);
----------------
Referenced View Columns:
"SEG_VIEW_PARENTS" ("SEG_NAME", "CODE", "NAME")
Referenced Table Columns:"UIP_SEGMENT_VALUES"
"SEG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_DESC" VARCHAR2(200 BYTE),
"SEG_TYPE" VARCHAR2(20 BYTE),
"SEG_COMPANY" VARCHAR2(20 BYTE)
I am having an issue when trying to union all between two databases.
This is the error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
I have updated the collation in both DB to Latin1_General_CI_AS and I am still getting the error when running the query.
The queries run separately ok. why I am still getting the collation error.
I have a SSIS package which contains a number of slowly changing dimension transformations. While the majority work I have one which gives me the following error 'Error: The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list. '. This error only occurs if the destination table holds data. If I truncate the table and reload the data then the package complete successful. The only difference I can see between this dimension transformation and the other dimension transforms is that the one in question has 2 business keys while the rest have 1.
Can anyone shed light on this?
Thanks
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
Hello,
How would you do a log in a massive rows loading, I'm having problems because every row error(because of casting, format, lookup) in a transformation task is redirected to a text file as a log, this is ok when only exist one error by row, but in the case when I have two errors in the same row detected by diferents transformation tasks only the first one is reported to the text file, I have to wait to the second information load, after I correct the first error, to find the second one, I need to validate as many errors exists by row in the same load...
which component or which strategy can I use in a SSIS Packge to achieve this?
thanks