SQL Server Performance

copy indexes and others

Discussion in 'SQL Server 2005 Integration Services' started by WingSzeto, Feb 13, 2007.

  1. WingSzeto Member

    I am new in integration service in SQL 2k5. After knowing what I can do in DTS in SQL 2K, naturally I assume there will be a similar way in BIDS with integration services in SQL 2K5. Here are my questions.

    1. What I like to do is export table structure including primary key, indexes, contraints, foregin keys, triggers within a table along with the permission settings from one db to another. How can I do this in BIDS?

    2. Say I want to import some data from an Excel file to a SQL table and like to clear the table before the import. In old DTS, I can just check a box for deleting the data in the export process. In SQL 2K5, the export option in SQL server mgmt studio is limited. If I need to do this in BIDS, do I need to create a dataflow task for deleting the data first? If this is the case, am I correct in assuming that most of the processes that used to be in import and export feature in sQL 2K are now replaced by tasks created in the data flow tab using tools that are available in toolbox of BIDS.

    3. I am importing data from an Excel file into a new SQL server table using BIDS. I would think that I would use the SQL server Destination in the data flow destinations toobox. For some reason, it gives me some data type conversion problem. I was surprised because I am using the dataflow tool to create a new table so why it would have a conversion problem. However, if I use 'OLE DB Destination' instead, I don't have such problem. why is that?

    4. if the source is an Excel file, is it true that whatever input data read from the Excel file by BIDS and subsequently created input defintion, I can't change that definition. For example, say the first column of Excel file is a datetime field, the corresponding field in the destination table has to match it. If there is a differnce, I can only change the definition in the destination field and can't change the source field.


    Wingman
  2. ranjitjain New Member

    Hi,
    Through management studio, right click your db and select the task of import or export data.
    It will open a wizard same like dtswiz in sql 2000.
    In that wizard you can select the option of appending data to destination table or delete all the data and then load new data.

    To create the table with all the indexes, in wizard itself you can specify your SQL script which will create indexes along with table and you even get the option to enable identity insert.

Share This Page