Stored procedure to Loop and Create a Separate text File Based on Key

[Fiat77]

I have a View in my SQL Server Database

CREATE VIEW dbo.VIEW_TESTDATA_ALL
AS
SELECT TOP 100 PERCENT G_RECORDS, SEQUENCING, G, VALIDATION, KEY_FIELD, RECORD_ID, CLASS_CODE, TERR_PREFIX, TERR, CLASS_CODE_2,
COINS, CONST, PC_PREFIX, PC, DED, RATE_GROUP, CLASS_LIMIT, EXP_PREFIX, EXPOSURE, ZIP_CODE, NUM_OF_STO, YR, BCEG
FROM DVessey.TestData
ORDER BY KEY_FIELD, SEQUENCING

What i need is a stored procdure that I can execute as a DTS Package that will LOOP and create a separate Text File to the path
O:\Practice

on every change in the Key_Field. Every Key_Field has 370 lines, the Sequence Number goes from 1 to 370.

Example Key_Field 5245200 grab its 370 records saves it as a Text File 5245200_Key.txt to the path O:\Practice
goes to the next Key_Field which might be 5245201 grabs its 370 records and saves is as a text file 5245201_Key.txt to the path O:\practice and continues all the way to the last Key_Field.

What Stroed procedure Code do I need to do this? Thanks

 

[JohnDTampaBay]

If a fixed width text file is all you need, you don't have to use DTS at all. Something like this should do the trick. Good luck!

CREATE PROC dbo.uspExportKeyFieldFiles
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @ThisKey int -- or appropriate datatype  
  DECLARE @BCPCmd varchar(1000)

  DECLARE curKeys INSENSITIVE CURSOR FOR
    SELECT DISTINCT Key_Field
    FROM DVessey.TestData
    ORDER BY Key_Field

  OPEN curKeys
  FETCH NEXT FROM curKeys INTO @ThisKey

  WHILE @@Fetch_Status = 0
  BEGIN
    -- extra line breaks added for readability
    SET @BCPCmd = 'bcp "SELECT G_RECORDS, SEQUENCING, G,
          VALIDATION, KEY_FIELD, RECORD_ID, CLASS_CODE,
          TERR_PREFIX, TERR, CLASS_CODE_2, COINS, CONST,
          PC_PREFIX, PC, DED, RATE_GROUP, CLASS_LIMIT,
          EXP_PREFIX, EXPOSURE, ZIP_CODE, NUM_OF_STO,
          YR, BCEG
          FROM DVessey.TestData
          WHERE Key_Field = '''
        + Cast(@ThisKey AS varchar(10))
        + ''' ORDER BY SEQUENCING" queryout "O:\Practice\'
        + Cast(@ThisKey AS varchar(10))
        + '_Key.txt" -c -T'

    EXEC master.dbo.xp_cmdshell @BCPCmd

    FETCH NEXT FROM curKeys INTO @ThisKey
  END

  CLOSE curKeys
  DEALLOCATE curKeys
END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Fiat77]

Thanks so much. One correction my bad we need to create Excel files on the extract, not Text files. How can i adjust this to make Excel Files? Thanks

 

[Fiat77]

How is it possible to connect database from program started within xp_cmdshell ? User, who executes xp_cmdshell, is not in sysadmin role and thus uses local (server) account SQLAgentCmdExec ?

 

[JohnDTampaBay]

BCP will only export to text files, although the files import nicely into Excel. sp_ExecuteSql can only be executed by sysadmin. It sounds like you need a DTS solution. Looping in DTS is a little cumbersome. Check out

http://www.sqldts.com/default.aspx?246

for an example.

My solution would be to create a package that exports a single key's data to its XLS file, then wrap that in the looping logic. You can use a Dynamic Properties task to assign a value from the database to a global variable (for instance the next key value to process). You can use an ActiveX script update properties of other objects from the global variable (like the output filename for the datapump destination).

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)