SQL Server AWS S3: Copy S3 File to a Different Bucket

Back to Index

Demonstrates how to copy an S3 file from one bucket to another. The file can also be renamed if desired. (The file already exists in S3 in one bucket, and is copied without downloading/uploading to another S3 bucket.) This example copies from /chilkat.qa/starfish.jpg to /chilkat.ocean/starfishCopy.jpg

Documentation: https://docs.aws.amazon.com/AmazonS3/latest/API/RESTObjectCOPY.html

CURL Command

curl -X PUT https://chilkat.ocean.s3.us-west-2.amazonaws.com/starfishCopy.jpg \
    -H "x-amz-copy-source: /chilkat.qa/starfish.jpg"

SQL Server Example

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    DECLARE @authAws int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.AuthAws', @authAws OUT

    EXEC sp_OASetProperty @authAws, 'AccessKey', 'AWS_ACCESS_KEY'
    EXEC sp_OASetProperty @authAws, 'SecretKey', 'AWS_SECRET_KEY'
    EXEC sp_OASetProperty @authAws, 'Region', 'us-west-2'
    EXEC sp_OASetProperty @authAws, 'ServiceName', 's3'
    EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws

    --  URL: https://chilkat.ocean.s3.us-west-2.amazonaws.com/starfishCopy.jpg
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat.ocean.s3.us-west-2.amazonaws.com', @port, @bTls, @bAutoReconnect
    IF @success <> 1
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ConnectFailReason', @iTmp0 OUT
        PRINT 'ConnectFailReason: ' + @iTmp0
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        RETURN
      END

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'x-amz-copy-source', '/chilkat.qa/starfish.jpg'

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'PUT', '/starfishCopy.jpg', @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END
    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Response Body:'
        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @xmlResponse int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xmlResponse OUT

    EXEC sp_OAMethod @xmlResponse, 'LoadSb', @success OUT, @sbResponseBody, 1

    --  See the Online Tool for Generating XML Parse Code
    DECLARE @CopyObjectResult_xmlns nvarchar(4000)

    DECLARE @tagPath nvarchar(4000)

    DECLARE @LastModified nvarchar(4000)

    DECLARE @ETag nvarchar(4000)

    EXEC sp_OAMethod @xmlResponse, 'GetAttrValue', @CopyObjectResult_xmlns OUT, 'xmlns'
    EXEC sp_OAMethod @xmlResponse, 'GetChildContent', @LastModified OUT, 'LastModified'
    EXEC sp_OAMethod @xmlResponse, 'GetChildContent', @ETag OUT, 'ETag'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @xmlResponse


END
GO

Sample XML Response Body

<?xml version="1.0" encoding="UTF-8"?>
<CopyObjectResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
    <LastModified>2018-12-18T15:53:32.000Z</LastModified>
    <ETag>"2e9c59dbf2662367dc97dfdda85da048"</ETag>
</CopyObjectResult>