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,
Please mark as answer if you find the post useful
My Blog: Akshay's Notion
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.
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.
Please mark as answer if you find the post useful
My Blog: Akshay's Notion
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.
-Tab Alleman
Marked as answer by akshay22 on May 07, 2012 04:34 PM
akshay22
Participant
914 Points
184 Posts
SSIS Copy Column to Various Destinations based on Condition
May 04, 2012 02:00 AM|LINK
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,
My Blog: Akshay's Notion
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 04, 2012 02:16 PM|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.
akshay22
Participant
914 Points
184 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 07:35 AM|LINK
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.
My Blog: Akshay's Notion
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 12:47 PM|LINK
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
akshay22
Participant
914 Points
184 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 12:59 PM|LINK
Is it possible for you to paste down the relevent section. Code Project is not opening up from my work place.
Thanks,
My Blog: Akshay's Notion
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 01:03 PM|LINK
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(@"D:\SSIS_ASP_NET\SSIS_ASP_NET_DEMO\SSIS_ASP_NET_DEMO\Package1.dtsx", null); //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 package.Connections["SourceConnectionExcel"].ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=Excel 8.0; "; //Execute DTSX. Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute(); } catch (Exception ex) { throw ex; } finally { package.Dispose(); package = null; } }akshay22
Participant
914 Points
184 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 01:10 PM|LINK
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.
My Blog: Akshay's Notion
TabAlleman
All-Star
15557 Points
2698 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 01:27 PM|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).
That's it.
akshay22
Participant
914 Points
184 Posts
Re: SSIS Copy Column to Various Destinations based on Condition
May 07, 2012 04:34 PM|LINK
Thanks for you help
My Blog: Akshay's Notion