how to get the duplicate file in ssis

is it possible that i can retrieve the list of all records that are duplicate and put them in a excel files

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

|||

Hi

thanks for the reply, It work nice, somehow it disappoint me a bit (not on your answer), cause i thought i won't use the query anymore to disect the data and I thought ssis has tool for this. Anyway thanks!!!

Jagapathi Raju wrote:

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

How to get status of sp_executesql

Hello

The basic requirement is to import data from excel sheet and save it to a table. The excel filename and sheet name is passed in as a parameter to a stored procedure. I am using sp_executesql to execute the statement.

First I delete all the records from the table and then copy the data from excel into the table. I want to do this as a transaction so that if there is any error in the import process, I want to rollback the transaction otherwise commit it. sp_executesql returns 0 for success or 1 for failure.

In my case, even if there is any syntax error, I get the status of 0. Also the @.@.ROWCOUNT contains some positive number greater than 0 even if some error occurs.

BEGIN TRANSACTION T1

GO

DELETE FROM SORTGROUP

GO

DECLARE @.Statement as NVARCHAR(1024)

DECLARE @.TableName as VARCHAR(32)

DECLARE @.ExcelFile AS VARCHAR(512)

DECLARE @.ParamNameList as VARCHAR(1024)

DECLARE @.SheetName as VARCHAR(64)

DECLARE @.Status as INT

DECLARE @.ErrorCode as INT

DECLARE @.RowCount AS INT

SET @.TableName = 'SORTGROUP'

SET @.ExcelFile = 'E:\PROJECTS\RALEIGH\TEST.XLS'

SET @.ParamNameList = '*'

SET @.SheetName = 'SORTGROUPDATA'

EXEC master..xp_sprintf @.Statement OUTPUT, 'INSERT %s SELECT %s FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE=%s'', ''SELECT %s FROM [%s$]'')', @.TableName, @.ParamNameList, @.ExcelFile, @.ParamNameList, @.SheetName

SET @.Status = -1

exec @.Status=sp_executesql @.Statement

SELECT @.ErrorCode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT

IF @.Status = -1

ROLLBACK TRANSACTION T1

ELSE COMMIT TRANSACTION T1

PRINT @.ErrorCode

PRINT @.RowCount

PRINT @.Status

Is there any reason why you're NOT using DTS to do this? The work flow management will handle all of that for you

How to get started

Hi -
I need to start from the beginning with SQL Server 7 2005 Express. I want to get financial data and perform analysis in conjunction with Excel. I have downloaded the file from Microsoft and would like a resource for getting going. What is the best way to go?

Your help appreciated.

Doug

Loads of information on the microsoft sites:

http://msdn2.microsoft.com/en-gb/express/aa718391.aspx

http://msdn2.microsoft.com/en-gb/sql/Aa336346.aspx

http://blogs.msdn.com/sqlexpress/

Books Online is a great resource:

http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

HTH!

how to get schema

Hi,

Could anyone tell me on how to get schema of a table(s) into excel or even simple well formated. text file. I tried scripts but seems complicated.

All i want is field name, datatype, size... That's it.

Thank you.

The following query will help you..

Select
T.Name TableName
,C.Name ColumnName
,Ty.name DataType
,C.Length
,C.Prec
,C.Scale
,Case When C.IsNullable =0 Then 'NULL' Else 'NOT NULL' End as Nullable
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType
Where
T.Type='U'
Order By
T.Name,
C.Colorder

|||I owuld prefer using the INFORMATION_SCHEMA.COLUMNS view.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How To Get HTML Rendering

I am using a ReportViewer with SQL Server Express. The ReportViewer provides options for output as Excel or PDF but not HTML. According to what I have read, SQL Server Express supports HTML rendering. How do I get it to do that?Let me clarify my own post. I have HTML-formatted text in two columns in the database. I need to have those two columns format as HTML in their respective textboxes. I've done a lot of reading on this issue and no one seems to have a very good solution.