October 1, 2012 at 2:02 am
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
October 1, 2012 at 2:22 am
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;
}
}
}
October 1, 2012 at 2:43 am
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
October 1, 2012 at 2:52 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply