SQL Server Performance Forum – Threads Archive

Insert default value when import data into table

Hi Folks I am importing excel data sheet into existing table. file has less columns then table and some of the columns are not null in the table. Therefore, I need to add value to the Not Null columns in the table at time of import data from DTS package. I have looking into the options are available on the left pane but some how could not figure out. so please help me and also reccommend me the book on SQL Server 2005 Integration Service Thanks in advance
sonny

Consider specifying a default value on the field definition in the table schema.

Thanks chappy I knew this is the way tackle this situation but I want to know that if we can do the time of import data using SQL server 2005.. Ihave another issue is that I ave remove all the constarints from table and need to add constarints after import the data.. e.g. not null and then composite primary key. suggestion please… Thanks
sonny

You can try ways…
1. Insert excel data into a permanent temp table without changing anything…
2. Insert into the destination table using permanant temp table and you check the the null value and then replace with any defaul value using ISNULL function… OR You can use OPENROWSET to read excel and insert into table and you can ISNULL function to replace null values with default value… http://www.databasejournal.com/features/mssql/article.php/3331881
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi I am runnig just simple insert from excel file into table… Insert into process_upload1
Select * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:csat
etgearAgent wise Parameter wise Raw format 09 apr 07.xls;HDR=YES’,
‘SELECT * FROM [Sheet1$]’) I am having this following error… Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ reported an error. The provider did not give any information about the error. Is this cos of space in file name(Agent wise Parameter wise Raw format 09 apr 07.xls)?
and if yes then how should i handle these spaces in filename? Thanks

Hi Folks I have another issue relatd with it as I am loading excel file from the so called folder into table. I am loading the data into temp table and then shift the required data into main table and I have created SSIS package for it. But problem is that if there are more than 1 file in the folder to load in a day then how could this be possible using SSIS. I mean… I need to check first of there are more than 1 file in the folder etc.. please help..
Thanks

You can use xp-cmdshell ‘dir folder path’ …. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Folks As usual requirements are changed… what I need to do now is
1. load csv file of current date from specific folder.
2. If file not found then send the mail message to the relevant persons or If exist then check file is of current date and load it.
3. Then check if file exist after an hour.. again if file not exist sent message again or If exist then check file is of current date and load it.
4. check again if file exist if not raise serious alert or If exist then check file is of current date and load it. I have done the databse mail and load part with stage table. The only part I need help for is check the exists of file for current date and load it. Thanks in Advance

]]>