none
How to object type variable values in Dataflow pipeline

    Question

  • Hi Everyone,

    I am facing issues when passing object type variable values in data flow task.

    Scenario: I have one requirement export data into excel file based on frequency basis like daily,weekly and monthly.

    For that i have created one table with columns like below

    RowID  ExcelFileName    Query                       Frequency
    1          ABC                   select *from ABC      DAILY
    2           DEF                  select *from DEF      DAILY        
    3           GHI                  Select *from GHI      WEEKLY
    4           jkl                    SELECT *FROM JKL    MONTHLY
    .            .
    .            .    
    .            .
    .            .
    50           RTY                SELECT *FROM RTY       Monthly

    My package flow is

    Step1: By using Execute Sql Task get Query and ExcelfileName and values pass to object type variable

    Variable

    Name: FullResult  Type:object

    Step2: Using ForEach loop container get the First Query and Excelfilename

    Variables

    Name:Query Type:string

    Name:excelname type :string

    Step3: Using Execute sql task create sheet with name as excelname

    Upto Step3 process exeucte success

    Next i want to use Query variable value in OLE DB Source.

    I am facing below error:

    [SSIS.Pipeline] Error:
     "OLE DB Source.Outputs[OLE DB Source Output]" contains no output columns. An asynchronous output must contain output columns.

    Please share your suggestions on this.



    Vaishu


    • Edited by Vaishu00547 Friday, July 04, 2014 11:23 AM Added some more details
    Friday, July 04, 2014 10:35 AM

Answers

All replies

  • Vaishu,

    Based on your description it looks like you are trying to setup dynamic data flow at runtime. This will not work because the standard Data Flow Task supports only static data flow defined at design time.

    You have a couple of options:

    - Implement code to generate SSIS packages dynamically. This is complicated and requires good understanding of the SSIS API. You may check EzAPI which can help with the generation.

    - Use third-party tool BIML to generate packages.

    - Use the commercial COZYROC Data Flow Task Plus. This is an extension of the standard Data Flow Task and it supports dynamic data flows at runtime. No programming skills are required.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, July 04, 2014 11:39 AM
  • The way you've it now, the only way you can implement it in SSIS using standard tasks is to use OPENROWSET to fill the excel sheet with the query result. You can form a dynamic string using your query and excelname and pass it as the query to Execute SQL task and it will populate excel sheet with your query data. You cant use data flow task as metadata is not fixed.

    The query expression would look like

    "INSERT OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=" + @[User::YourExcelFilePath] + "',
    'SELECT * FROM [" + @[User::YourSheetName + "$]') " + @{User::YourQuery]

    Assuming you've used string type variable to store excel filename, sheet name and your query

    You'll create a variable (say @[User::ExcelQuery]) with EvaluateAsExpression property true and using above expression

    Then inside the ExecuteSQLTask use ConnectionType AS OLEDB, SQLCommandType as variable and map to above variable.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Friday, July 04, 2014 12:04 PM
  • Thanks Visakh,

    Currently Production process using OPENROWSET() but some times process running good and some times process is failing and i am getting below error:

    Msg 7403, Level 16, State 1, Line 2

    The OLE DB provider "Microsoft.ACE.OLEDB.12." has not been registered.

    Due to Reason I want update same process in SSIS.

    Regards,


    Vaishu

    Monday, July 07, 2014 12:55 PM
  • Thanks Visakh,

    Currently Production process using OPENROWSET() but some times process running good and some times process is failing and i am getting below error:

    Msg 7403, Level 16, State 1, Line 2

    The OLE DB provider "Microsoft.ACE.OLEDB.12." has not been registered.

    Due to Reason I want update same process in SSIS.

    Regards,


    Vaishu

    Is it a 64 bit system?

    See if this helps

    http://visakhm.blogspot.in/2011/11/excel-2010-export-import-issue-in-64.html

    http://visakhm.blogspot.in/2013/12/how-to-solve-microsoftaceoledb120-error.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 07, 2014 1:09 PM