How to filter data while being imported from excel to table via DTS

[Jeet2004]

How/where can i filter out bad data or implement certain business rule in DTS package so that the job doesnt fail becasue of bad data.
Thanks.

 

[DrSql]

Yes, you can filter.
Right Click on the filer, Select Properties.
Click Build Query button.
Select the fileds and add the filter at the bottom.
The you need to regenerate the Destination and Transformation. If you need any clarificatiosn let me know.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[Jeet2004]

thanks for your reply but still i am not clear about
How do i get a filter ?
Also what i really need is that in the table there are two columns which do not allow null (one is integer type and other is date type)and the file has some rows with that two or one of the two columns as null so the job fails . I want that either
1. it should skip that row and insert that row in an exception file or something
or
2. insert a 9999 or 99991999 whenever its null or not of datatype required.

how can this be achieved in dts package.
Thanks in advance for your help.

 

[DrSql]

Can you be more specific what you are trying to accomplish. May be I am confused and going in wrong direction.
In DTS package you can execute SQL statements and stored procedures. If you can make more clear I can give you more details. I have done various Stored Procedures and DTS packages to transfer between tables and files.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[DrSql]

Try this and see all you data is showing up.
declare @path varchar(255)
--Path where your text file is located.
select @path = 'type C:\Test.txt
--create temptable
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temptbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temptbl]
GO

CREATE TABLE [dbo].[temptbl] (
[Data] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
*/
insert into tempTable exec master..xp_cmdshell @path

select * from tempTable

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[Jeet2004]

I have 5 columns in the excel file and sql table but the problem is out of 5, three columns dont allow nulls in them
so even though if not all columns have values in a row in the excel sheet when the row is inserted in the table it should fill the column with some value ( i was wondering if i could use default values for that ) as right now the dts package fails whenever it encounters a null or wrong datatype for these coulmns.(one of the column is date data type and one is integer and other is varchar).

what i need is that the package shoudl not fail or not stop because of any error in the data and if it encounters the error like above ( because of null or wrong data type) it should either write it out to an exceptio file and continue operations or is should insert some defualt value for those nulls or wrong data type)
Thanks.
NOTE: If i am still not clear please let me know and ill email you the exact files i am working on.
THANKS A LOT FOR UR HELP

 

[DrSql]

This is few ways to approach, I am not sure which one would help you more, both options needs to tested according to your environment.
Option1:
Try the belopw statement in you DTS SOURCE
/* Need to rename the Col names */
SELECT COL1= case when COL1=NULL then 'XXX' end,
COL2= case when COL2=NULL then 'XXX' end,
COL3= case when COL3=NULL then 'XXX' end,
COL4= case when COL4=NULL then 'XXX' end,
COL5= case when COL5=NULL then 'XXX' end
from EXCELSHEET
Option2:
Use the Same DTS package
Step1:
From the Excel sheet Export into a temp table with 5 columns.
Step2:
Execute SQL statement and to update all Null values with your desired vlaue
Then Export to that data from temp table to your table
Or
INSERT INTO PRODTABLE
SELECT COL1= case when COL1=NULL then 'XXX' end,
COL2= case when COL2=NULL then 'XXX' end,
COL3= case when COL3=NULL then 'XXX' end,
COL4= case when COL4=NULL then 'XXX' end,
COL5= case when COL5=NULL then 'XXX' end
from TEMPTABLE

Let me know if you need any clarifications

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.