SQL Server Performance

Creating data load package - need some help...

Discussion in 'SQL Server 2005 Integration Services' started by sql_jr, Feb 4, 2009.

  1. sql_jr New Member

    Hi, I need some help to have my SSIS package do the following things. Please provide some SPECIFIC details:
    · Read the 2 files received daily· Load them into staging tables
    · Append the new data to existing table
    · Move the files from main directory to a history folder and rename the files with a date stamp on it
    Specifically, 1) is there a way to map columns to data using a metadata format file (like BCP), or is it best to map the columns in advance.
    2) On the last item, I'd like it to (a) read a file name that looks like MyFile1.bcp.020202 (it reads it as text flat file) and be able to parse out the "MyFile1" part renaming it to a .txt. So, step one would be to check for existing file "MyFile1" --> Rename it to .txt --> Load the data --> Move file to History folder and rename it with datetime stamp. I think you can use file task, but need tech advice.

    Please advise on the above! Many thanks!
  2. MohammedU New Member

    Why don't you use BULK INSERT using dynamic SQL to parse the file names and loading the data into staging table and once loaded you can rename and copy the file using xp_cmdshell...

Share This Page