SQL Server Performance

Insert default value when import data into table

Discussion in 'SQL Server 2005 Integration Services' started by sonnysingh, Apr 9, 2007.

  1. sonnysingh Member

    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
  2. Chappy New Member

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

  3. sonnysingh Member

    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
  4. MohammedU New Member

    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.
  5. sonnysingh Member

    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
  6. sonnysingh Member

    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
  7. MohammedU New Member

    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.
  8. sonnysingh Member

    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

Share This Page