Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import file from HTML source Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 2:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
Hi

I have a subscription to a company that provides a csv file from a HTML link which i currently download using some vba in an access db which then get's moved to our SQL db.

Since i have recently gained some SSIS experience i thought i'd try and move over to SSIS fully. I've set up a HTML connection to the file which has tested ok.

My question is how to create a HTML connection as the source file? I thought flat file would do the trick but can't get that working?

I'm using sql 2005

Thanks
Post #1366368
Posted Monday, October 1, 2012 2:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
We do exactly the same thing, but we download the files first then use a for each loop to process them all.

We use a script task with the following code, which uses a static variable to set the path to download to.

Hopefully should be easy enough to understand the below

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_346acc0b5c1843a7be32c7b7990d107a.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion


public void Main()
{
// TODO: Add your code here
Microsoft.SqlServer.Dts.Runtime.HttpClientConnection httpConn;
Object obj;
String strSourceFileFullPath;
strSourceFileFullPath = Dts.Variables["v_strSourceZipFileFullpath"].Value.ToString();

try
{
obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
httpConn = new HttpClientConnection(obj);
httpConn.DownloadFile(strSourceFileFullPath, true);
}
catch (Exception e)
{
Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message, "", 0);
}
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;

}
}
}





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366385
Posted Monday, October 1, 2012 2:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
thanks for the reply

when i paste in your code everything has lines underneath.

when i go into the script mine looks like this..

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime


Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

am i using a different language? thanks
Post #1366393
Posted Monday, October 1, 2012 2:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
Thats a VB script that your editing, mine is a C# script, if you change the type on the properties of the task you can change it between C and VB.

Note mine is written in 2008 not 2005 so might be a few differences but the theory is there and should work.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1366398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse