Tuesday, July 23, 2013

SSIS - connection string from XML

SSIS - getting connection string from XML file.
Step 1
Go to SSIS and select Package Configurations.
 Step 2
Add new configuration.
Step 3
Select Configuration type as XML Configuration file. Then select Specify configuration setting directly radio button and select the configuration file.
Step 4
Select the property which you want to configure. Here it is Connection string. Then click next.
Step 5
Give a name to this configuration.  And click on finish.
Hey you are done with this in just 5 click.
Here is the configuration file (abc.dtsConfig)

<?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="HCLTECH\hriday.dutta" GeneratedFromPackageName="Package" GeneratedFromPackageID="{9F34C6C8-7D33-4E60-AA5A-1A95DA62D08B}" GeneratedDate="7/17/2013 2:48:57 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=;Initial Catalog=master;User ID=abc;Password=abc</ConfiguredValue> </Configuration> </DTSConfiguration>


Monday, July 22, 2013

Export to excel in ZIP format

Exporting large file from Asp.Net pages cause huge bandwidth consumption, slow response and time-out problem. In most of application there are couple of pages
where we export large data file in to excel. And the most common problems are response time-out and  slow UI response.
There are two solutions for this problem.
1. Enable streaming for download.
2. Download file in Zip format.
Streaming is considered to be the best solution for downloading. But it need a dedicated streaming service(WCF/SOAP) and lots of working hour to implement this.
Streaming does not support all file types like .xls, .txt etc.
The second solution is zip. Which can be applied with all file types. And compared to streaming, it is quite easy to implement with couple of lines of code.
Zip give us up to 95% compression for excel files. So we really need not to worry for file size up to 100 MB in case of excel.  
Now the question is which solution we should implement in our application. Here I must say Zip if it is not a video/audio.
Step 1
Put a button control in our .aspx page.
Step 2
Import DotNetZip library. You can download it from CodePlex.
It is an Open Source Library with Microsoft Public License (Ms-PL).
Step 3
Import Iconic.Zip namespace to your code behind.
Step 4
On button click write the following code.
This is all you need to do for download file in zip format.

Execute SSIS package from ASP.NET

Execute SSIS package from ASP.NET web forms. Let’s take a scenario, we will create a table in SQL server and insert data into that table using SSIS package and call the package from Web forms.
Step 1
Create a table in SQL server named “stud”.
Step 2
Prepare a parameterize insert SQL query.
Step 3
Create a SSIS package. Open SQL Server Business Intelligence Development Studio. Then create a new “Integration Services Project”.
Step 4
Drag a “Execute SQL Task” control in Control Flow tab.
Step 5
Right click on “Execute SQL Task” and select Edit. And Execute SQL Task Editor will popup.
Step 6
Now we need to configure Connection Type and Connection. For this case we use ADO.NET. And Configure the Connection.

Step 7
After configuration the connection type and connection string, now it’s time to configure “SQL Statement” and Parameter Mapping. Put the prepared  parameterize insert SQL query in “SQL Statement” block.
Before Parameter mapping done we need to create two variable so that we map them with parameter. For this right click on any blank space and create two string type variable Name and Roll.

Now Go to Parameter mapping and map these two Variable with  our two parameter.
Your are done with SSIS package. Now make a quick build and check everything is ok. You will find our SSIS package under Bin folder (“Package.dtsx).
Step 8
Now create and an Asp.Net web application and call the SSIS Package. Copy the Package.dtsx file to our solution. Take two textbox and a button control.
On the click event of the Button write the following code.
First off all you need to take reference of “Microsoft.SqlServer.ManagedDTS.dll”. You will find it in GAC.
Now include the namespace Microsoft.SqlServer.Dts.Runtime
Its time to test. Run the application.  With some input value .
SQL Tables
Mission accomplished.


You can download examples and source code from here.