SSIS Copy Column to Various Destinations based on Condition

Hi All,

In my current application I have a button to generate report based on certain filter criterion given on the page.

To do this I have a SSIS package which simply converts data from SQL Server to Access (using Copy Column) task based on the filter set above.

Now the requirement is to add a drop down which says "select the format" like .xls, .csv, .pdf, .mdb etc.

If a user clicks on the button to generate reports now he is given the report in the format he selected.

(I am not going with the SSRS approach because first of all I have huge data and secondly it doesn't allow us to save file in the access format - if I am not wrong)

Please give me suggestion as to how I can do this with SSIS reports. I am not an SSIS expert so would require a detailed one.

Thanks,

Re: SSIS Copy Column to Various Destinations based on Condition

Create a package variable, like @format or something that holds the value of the selected format.

After your data source component, put a Conditional Split component that looks at the package variable and send the rows to the appropriate destination component.

Look in the toolbox under destination compenents for your available options. You can use a flat file destination for csv, and obvious excel for excel. However, I don't believe you can export to .pdf using SSIS. The only reliable way I know to do that is with SSRS.

Re: SSIS Copy Column to Various Destinations based on Condition

Hi TabAlleman,

That was a really helpful piece of advice and it works for me.

However I also want to call this packages(which is doing the work I described above) through C# code.

In my page I have some filter conditions based on which I am constructing the "where" clause of my data source and the tough job for me is to modify the where condition of the DataSource of the package from C# code before running the package.

Please suggest me something on this.

Re: SSIS Copy Column to Various Destinations based on Condition

Again, use package variables in your WHERE clause. This article shows you (near the end) how to set SSIS package variables from .net:

http://www.codeproject.com/Articles/28096/Excecute-SSIS-package-DTSX-from-ASP-Net

Re: SSIS Copy Column to Various Destinations based on Condition

Is it possible for you to paste down the relevent section. Code Project is not opening up from my work place.

Thanks,

Re: SSIS Copy Column to Various Destinations based on Condition

Sure. Hopefully it's not illegal.

using Microsoft.SqlServer.Dts.Runtime;
        
       protected void btnExecute_Click(object sender, EventArgs e)
       {
            Application app = new Application();
            Package package = null;
            try
            {
            string fileName =
Server.MapPath(System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName.ToString()));
            FileUpload1.PostedFile.SaveAs(fileName);
            
            //Load DTSX
            package =
app.LoadPackage(@&quot;D:\SSIS_ASP_NET\SSIS_ASP_NET_DEMO\SSIS_ASP_NET_DEMO\Package1.dtsx&quot;, null);
            
            //Global Package Variable
            Variables vars = package.Variables;
            vars[&quot;Business_ID&quot;].Value = txtBusinessID.Text;
            vars[&quot;Business_Name&quot;].Value = txtBusinessName.Text;
            
            //Specify Excel Connection From DTSX Connection Manager
            package.Connections[&quot;SourceConnectionExcel&quot;].ConnectionString =
 &quot;provider=Microsoft.Jet.OLEDB.4.0;data source=&quot; + fileName + &quot;;Extended Properties=Excel 8.0; &quot;;
            
            //Execute DTSX.
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            package.Dispose();
            package = null;
        }
    }

Re: SSIS Copy Column to Various Destinations based on Condition

TabAlleman,

My query is to modify the "Where" clause of the "OLE DB Source" of my SSIS package from C#.

E.g.

Initially my query is

Select Name, Age

From Employee

Where Name <> ''

Now from my C# code I want to modify the query to

Select Name, Age

From Employee

Where Name <> '' and Age >= 30

(I want this because there can be many Where conditions based on the filter a user chooses in the page).

I don't think the articles explains this.

Re: SSIS Copy Column to Various Destinations based on Condition

The part of the article I copied dealt with passing parameters to a package from .net. I assumed you already knew how to use parameters in your DB Source.

So assuming you created a package variable called @WhereString and populated it with " and Age >= 30".

Your Query in SSIS would be this:

Select Name, Age From Employee Where Name <> '' ?

The question mark is a place-holder for a variable.

Then you go to the "Parameter Mapping" tab and map User::WhereString to parameter 0 (zero).

That's it.

Re: SSIS Copy Column to Various Destinations based on Condition

Thanks for you help