Last post May 07, 2012 04:34 PM by akshay22
May 04, 2012 02:00 AM|akshay22|LINK
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.
May 04, 2012 02:16 PM|TabAlleman|LINK
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.
May 07, 2012 07:35 AM|akshay22|LINK
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.
May 07, 2012 12:47 PM|TabAlleman|LINK
Again, use package variables in your WHERE clause. This article shows you (near the end) how to set SSIS package variables from .net:
May 07, 2012 12:59 PM|akshay22|LINK
Is it possible for you to paste down the relevent section. Code Project is not opening up from my work place.
May 07, 2012 01:03 PM|TabAlleman|LINK
Sure. Hopefully it's not illegal.
protected void btnExecute_Click(object sender, EventArgs e)
Application app = new Application();
Package package = null;
string fileName =
//Global Package Variable
Variables vars = package.Variables;
vars["Business_ID"].Value = txtBusinessID.Text;
vars["Business_Name"].Value = txtBusinessName.Text;
//Specify Excel Connection From DTSX Connection Manager
"provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=Excel 8.0; ";
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
catch (Exception ex)
package = null;
May 07, 2012 01:10 PM|akshay22|LINK
My query is to modify the "Where" clause of the "OLE DB Source" of my SSIS package from C#.
Initially my query is
Select Name, Age
Where Name <> ''
Now from my C# code I want to modify the query to
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.
May 07, 2012 01:27 PM|TabAlleman|LINK
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).
May 07, 2012 04:34 PM|akshay22|LINK
Thanks for you help