Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Inserting an autonumber through a DTS package

I have a DTS package that imports some data into a table from a flat text file. One of the columns in the table doesn't allow nulls (its an application table so the properties cannot be altered). How do I insert an autonumber into this field. The DTS package appends records to the table, so it would have to know how to start the autonumbering from the last number that was granted in the table. Thanks for any input.
0
isaacr25
Asked:
isaacr25
  • 6
  • 4
  • 2
  • +1
7 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the table's field not yet already a autonumber (identity?). would make life quite easy...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if not, the only way you have is to load into a staging table which has a identity field (each time reset to 1),
and then insert into the final table:

insert into final_table ( <"autonumber_field">,<fields>)
select <identity_field> + ( select max(<"autonumber_field">) from final_table )
, <fields> from staging_table

hope this helps
0
 
isaacr25Author Commented:
How do I set up a field to be an autonumber field in the staging table? Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
isaacr25Author Commented:
I have set up the field to be a numeric identity column. During the import, I get an error saying that I cannot import a null value into this field since it is an identity field. I would like for the field in the table to be populated automatically since I have nothing to bring in for that field in the file.
0
 
Anthony PerkinsCommented:
Make sure to not include the IDENTITY column in the Transformations tab and do not check the box that reads "Enable identity insert"
0
 
isaacr25Author Commented:
Thanks acperkins,
    I think that helped. Another question: Is there anyway to specify within a DTS package for the source file to be deleted each time the package is run? Also, on the flip side, how can I configure the DTS package to only append new data (any record from the source file that does not exist in the destination table)? Thanks.
0
 
Anthony PerkinsCommented:
>> Is there anyway to specify within a DTS package for the source file to be deleted each time the package is run? <<
You will have to create an ActiveX Script Task to run after the Transform Data Task completes successfully (see Workflow)

>>how can I configure the DTS package to only append new data <<
Use a staging table and then execute an Execute SQL Task to insert only the new rows.
0
 
isaacr25Author Commented:
>>Use a staging table and then execute an Execute SQL Task to insert only the new rows.<<

Can you provide me with some code for inserting only new rows? Here's another challenge. Since I'm creating an ID number (autonumber field) for each record in the staging table, I will have to exclude this field in my comparison of records to determine which ones to exclude in the import from the staging table to the real one. How can I do this? Thanks again.
0
 
Anthony PerkinsCommented:
>>Can you provide me with some code for inserting only new rows?<<
Without knowing your table structures or the layout of the text file, it is a little difficult being specific, but there is not a lot to it.  What you do is import all the rows into a temporary staging table that matches the text file layout.  You can then do an Insert statement into the final production tables using the staging table, based on some condition.
0
 
AmiiitCommented:
You can use this kind of condition to include only new rows.

Insert into MainTable (primaryKeyCol,col1,col2)
Select primaryKeyCol,col1,col2 from StagingTable a where not exists (Select 1 from MainTable b where a.primaryKeyCol=b.primaryKeyCol)


This statement will transfer only new records to the Main table from the staging table. you can use one primary key column or multiple as well.
0
 
isaacr25Author Commented:
Ok...
    Is there anyway to create a "fake" record, where I can say:

Don't import records where table1.col1.col2.col3 are the same as table2.col1.col2.col3? I can create a separate field in the staging table, but since the other table is an application table, I cannot make any changes to it.
0
 
isaacr25Author Commented:
>> Is there anyway to specify within a DTS package for the source file to be deleted each time the package is run? <<
You will have to create an ActiveX Script Task to run after the Transform Data Task completes successfully (see Workflow)


When I go to Workflow to delete the sourcefile each time the import is successful, its telling me that I need to select two or more items. I only have a Connection 1 (source file), then the transformation (the arrow), then a Connection 2 (destination file). No matter which combination of objects I select, I keep getting either "Select two or more items..." or "Defining precedences between the two selected items is not valid." Please help. Thanks.
0
 
Anthony PerkinsCommented:
Assuming that you have created the ActiveX Script Task, than
1. Right click on it and select Workflow | Workflow properties.  
2. Click on New and add the Transform Data Task in the Source Step with a Precedence of Success.  
3. Select OK
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now