SSIS newbie How to get data output to file

Question

OK so I'm a complete Novice to SSIS but i needed to export images stored in our DB relating to specific sales orders , I attempted to do this in as an SP in SQ but this required CURSORS and I found it very easy to do the same thing in SSIS but I have a bit of an odd question , The DataFlow OLE DB to Data Flow works fine but i have have had to Declare the path OUTPUT FILE LOCATION in the SQL . I have worked out how to create a Dynamic File creation On the control flow but what i cant work out is how to remove the declared PATH and point it to the Control flow FILE SYSTEM TASK . I really hope this make sense and I appriciate any assistance

Control Flow

Data Flow Task

Data Flow

SQL -

DECLARE @Path nvarchar(1000);

SET @Path = N'C:\Users\X-JonC\Documents\';

SELECT
    Products_IMAGEDATA.ImageData
,   Products_IMAGEDATA.ImageTitle
,   @Path + Imagetitle AS path
FROM
    SalesOrders
    INNER JOIN
        SalesOrderItems
        ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
    INNER JOIN
        Products
        ON SalesOrderItems.Product = Products.Product
           AND SalesOrderItems.Product = Products.Product
    INNER JOIN
        Products_IMAGEDATA
        ON Products.Product = Products_IMAGEDATA.Product
WHERE
    SalesOrders.SalesOrderId = ?

score 3 · Answer 1 · answered May 05 '19 at 22:08

A File System Task is something you use to perform operations on the file system (copy/rename/delete files/folders). You likely don't need a file system task unless you need to do something with the file after you've exported it to disk (like copy to a remote location or something).

A Data Flow Task is something you use to move data between multiple places. In your case, you'd like to move data from a database to a file system. The interesting twist is that you need to export binary/image data.

You have an excellent start by having created a data flow task and wired up an Export Column task to it.

The challenge that you're struggling with is how do I get my SSIS variable value into the package. Currently, you have it hard coded to a TSQL variable @Path. Assuming what you have is working, then you merely need to use the parameterization approach you already have with SalesOrderId ? and populate the value of @Path in the same manner- thus line three becomes SET @Path = ?;

One thing to note is that OLE and ODBC parameterization is based on ordinal position (0 and 1 based respectively). By adding in this new parameter placeholder in on line 3, it's now the first element as it comes before the WHERE clause's usage so you will need to update the mapping. Or you can be lazy and replace the empty line 2 with DECLARE @SalesOrderId int = ?; {this allows the first element to remain as is, you add your new element as +1 to the usage}. You'd need to replace the final question mark with the local variable like

DECLARE @Path nvarchar(1000);
DECLARE @SalesOrderId = ?
SET @Path = ?;

SELECT
    Products_IMAGEDATA.ImageData
,   Products_IMAGEDATA.ImageTitle
,   @Path + Imagetitle AS path
FROM
    SalesOrders
    INNER JOIN
        SalesOrderItems
        ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
    INNER JOIN
        Products
        ON SalesOrderItems.Product = Products.Product
           AND SalesOrderItems.Product = Products.Product
    INNER JOIN
        Products_IMAGEDATA
        ON Products.Product = Products_IMAGEDATA.Product
WHERE
    SalesOrders.SalesOrderId = @SalesOrderId;

Reference answers

SSIS newbie How to get data output to file

1 Answers1