Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

Browsing large files for SSIS process without uploading in ASP.NET

I hope one of you clever people can help me with the following problem, go easy on me as I am fairly new to ASP.NET and MS Visual Studio.

I am currently building a webpage which will be used to run a SSIS package.  I have built the code to run the package and this works fine.  I now want to pass a variable to the package which contains the path of the file to process.  Ideally the user will be able to select this file using a 'Browse File' dialog box.

As it stands I have used the 'FileUpload' tag to create the text box and button to allow the user to select the file.  I now wish to pass the file path to the SSIS package without uploading the file to the server as these files can be massive.  In the code I have attached is a simplified version of what I am trying to achieve which is currently just to pass the filepath to a label but what I find is that when the file is above 4mb the page fails to load after the button is clicked.  If the file is less than 4mb then this works fine.  I understand that you can change this in the webconfig settings but should I need to change this if I never need to upload the file?  Am I approaching this task the right way?

Any help in this matter is much appreciated.
'code snippet for webpag
<form id="form1" runat="server">

    <asp:FileUpload ID="fuFileASP" runat="server" />
    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick ="UploadProcess" />
    <asp:Label ID="lblMessage" runat="server" Text="Message Box"></asp:Label><br />

</form>

'VB code snippet
Protected Sub UploadProcess()

        lblMessage.Text = Server.MapPath(System.IO.Path.GetFileName(fuFileASP.PostedFile.FileName.ToString()))

End Sub

Open in new window

0
badabing1
Asked:
badabing1
  • 6
  • 4
1 Solution
 
rajvjaCommented:
Hi

Visit this link. http://www.codeproject.com/KB/database/Digging_SSIS_object_model.aspx?msg=2045175

It will give you clear picture to run the SSIS package from asp.net page
0
 
PedroCGDCommented:
0
 
badabing1Author Commented:
Thanks for the reply rajvja but this article is not answering my question.  Correct me if I am wrong but this article is explaining how to programmatically create an SSIS package.  I already have the package built and I have the means to run it, I simply need to allow the user to select a file to run it on.  The code I have attached works except for line 10 which attempts to take the file path from the 'FileUpload' html object, this fails when the file is above 4mb, if I replace this line with line 11 it works as the location is hardcoded.

Thanks Pedro for your response also, this is much closer to what I am trying to achieve but again is not answering my question.  This article shows you how to run a SSIS package from asp.net but doesnt give me information on passing the file path as a variable from the html file upload object. I hope the aditional code and the paragraph above makes this more clear.
        Dim pkgLocation As String
        Dim pkg As New Package
        Dim app As New Application

        lblMessage.Text = "Processing"
        pkgLocation = "C:\Documents and Settings\graham.clements\My Documents\Visual Studio 2005\Projects\BillUploader\BillUploader\BTRetail.dtsx"
        pkg = app.LoadPackage(pkgLocation, Nothing)
        pkg.Variables("strFile7").Value = fuFileASP.PostedFile.FileName
        'pkg.Variables("strFile9").Value = "C:/MyFileLocation/MyFile"
        pkg.Execute()
        lblMessage.Text = "Processing Complete"

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
badabing1Author Commented:
*I made a mistake on the line numbers in my previous comment, it should be line 8 fails but it works when it is replaced with the currently commented out line 9. :-S
0
 
PedroCGDCommented:
problem resolved!?
0
 
badabing1Author Commented:
Unfortunately not.  I have changed the maxrequestlength parameter in my webconfig from 4096 to 900000, which will now allow files up to 900mb to be uploaded.  This works but with large file to simply show the file path of the file using msgbox(fuFileASP.PostedFile.FileName) takes quite a while, which suggests to me that the file is being uploaded to the server, I do not want this to happen as all I need to do is pass the file name from the fileupload object as a variable to the SSIS package.  

So I'm still stuck and am questioning if using the 'FileUpload' html object is the right tool for users to browse for the files the SSIS package will process as they do not need to be uploaded.
0
 
PedroCGDCommented:
Is an intranet?! Why you dont store the files in a filesystem share!?
Regards,
Pedro
0
 
badabing1Author Commented:
Yes it is an intranet.  The files are telephony billing files which could be located on anyone's computer.  My ideal solution is for the user to browse to this file, press a button and then the file's full UNC path is sent to the SSIS package to be processed.  I do not want the file to be uploaded anywhere.  By using a share would the user still not need to browse to the file and then select it using the fileupload on the webpage?

My solution actually works but it is uploading the file to the server each time which is unneccesary and time consuming as the files are often very large (<300mb).  Idealing I need to 'switch off' the upload element of the fileupload html object.
0
 
PedroCGDCommented:
and the problem is sending a parameter to a SSIS variable in the package, correct?! If true in the link above is an example...
regards,
Pedro
0
 
badabing1Author Commented:
No my friend, that has never been the problem.  The problem is with the asp.net 'fileupload' element, it is uploading the files when all I want is the chosen file's path to pass to the SSIS package.

I believe I have finally solved this using a couple of resources.  This problem has had me tearing my hair out for the last few weeks so I'll go on to explain my solution in case anybody else has the same problem.

First I used the solution given by prairiedog found here: http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23986248.html?sfQueryTermInfo=1+asp.net+brows+file+upload
In particular the article contained within: http://www.techproszone.net/blog/?p=222 

This seemed to be the perfect solution but I found that when you click the button tied to running the SSIS process the onclick event didnt fire.  Instead it would clear the contents of each 'fileupload' control on the page, as I had 2 it would take 3 clicks to fire the onclick event.  I decided I needed to clear the fileupload controls myself programmatically so I used this article: http://www.dotnetspider.com/resources/2768-clear-value-ASP-NET-FileUpload-Control.aspx

Once all the fileupload control were cleared the onclick event fired immediately.  I cannot explain why this happened, maybe someone else can but nevertheless it is solved.  I have added my complete code, its needs some tidying but hopefully will point others in the right direction if they are having the same problem.

'webpage code
<title>Untitled Page</title>
    
    <script language = "javascript" type = "text/javascript" >
        function OpenFileDialog(UploadFile)
        {
            if (UploadFile == 1)
            {
            var file = document.getElementById('<%=fuFile1.ClientID %>');
            var txt = document.getElementById('<%=txtFile1.ClientID %>');
            }
            else
            {
            var file = document.getElementById('<%=fuFile2.ClientID %>');
            var txt = document.getElementById('<%=txtFile2.ClientID %>');
            }
            file.click();
            txt.value = file.value;
               
            //Clear the contents of fileupload element   
            var file2= file.cloneNode(false);    
            file2.onchange= file.onchange;    
            file.parentNode.replaceChild(file2,file);
        }
    </script>
    
</head>
<body>


    <form id="form1" runat="server">
    
        <div>
            <asp:FileUpload ID="fuFile1" runat="server" />
            <asp:FileUpload ID="fuFile2" runat="server" />
        </div><br />
        <div>
            Select a file to upload:<br />
            <asp:TextBox ID="txtFile1" runat="server"></asp:TextBox>
            <input id="btnBrowseFile1" type="button" value="button" onclick ="return OpenFileDialog(1);" /><br />
            <asp:TextBox ID="txtFile2" runat="server"></asp:TextBox>
            <input id="btnBrowseFile2" type="button" value="button" onclick ="return OpenFileDialog(2);" />
        </div>
        
    <asp:Button ID="btnProcess" runat="server" Text="Process" OnClick="UploadProcess" />
    <asp:Label ID="lblMessage" runat="server"></asp:Label>

    </form>

    <asp:SqlDataSource ID="dsBillUpload" runat="server" 
        ConnectionString="Data Source=AL-SQL1;Initial Catalog=BillUpload;Integrated Security=True" 
        ProviderName="System.Data.SqlClient" 
        SelectCommand="SELECT BillID, Provider, CustRef, BillName, BillDate, BillGroup, UploadDate, BilledAmount FROM Bill">
    </asp:SqlDataSource>
</body>


'behind code
    Protected Sub UploadProcess()

        Dim pkgLocation As String
        Dim pkg As New Package
        Dim app As New Application

        lblMessage.Text = "Processing"
        pkgLocation = "C:\MyPackageLocation\MyPackage.dtsx"
        pkg = app.LoadPackage(pkgLocation, Nothing)
        pkg.Variables("strFile7").Value = txtFile1.Text
        pkg.Variables("strFile9").Value = txtFile2.Text
        pkg.Variables("strFile8").Value = ""
        pkg.Variables("strFile3").Value = ""
        pkg.Execute()
        lblMessage.Text = "Processing Complete"
End Sub

Open in new window

0
 
badabing1Author Commented:
Solution found.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now