c# - 如何修复从Yahoo下载报价文件并插入数据库的脚本任务代码？

Question

在给定的SSIS脚本下方，您需要输入一个股票代码和日期范围，然后返回一个CSV格式的下载文件，该文件可用于提取价格历史记录，但它不起作用，我也不知道为什么。在下面的链接中可以找到有关此SSIS深思熟虑概念的完整信息。

SSIS / ETL Example – Yahoo Equity & Mutual Fund Price History

您可以从下面的链接下载示例SSIS包。

Sample package on SkyDrive

以下SSIS脚本任务没有错误，但不会下载文件：

我刚刚开始理解此代码，似乎此链接已细分为此处的组件，并且具有正确的值，它必须可以工作，但我不明白为什么它不检索文件。

ichart.finance.yahoo.com/table.csv?s={symbol}&a={startMM}&b={startDD}&c=
{‌​startYYYY}&d={endMM}&e={endDD}&f={endYYYY}&g={res}&ignore=.csv

我正在使用的脚本任​​务代码：

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Configuration;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Net;
using System.Collections.Specialized;
using System.Linq;

using Hash = System.Collections.Generic.Dictionary<string, string>;

namespace ST_361aad0e48354b30b8152952caab8b2b.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

        static string dir;
        static DateTime end;
        const string CSV_FORMAT = "Id,Cusip,Date,Open,High,Low,Close,Volume,Adj Close";

        public void Main()
        {
            // end date is today minus one day (end of day)
            end = DateTime.Now;

            // output directory stored in SSIS variable
            // which can be set at runtime
            dir = System.IO.Path.Combine(Dts.Variables["OutputCSV"].Value.ToString(), end.ToString("yyyyMMdd"));
            if (!System.IO.Directory.Exists(dir))
                System.IO.Directory.CreateDirectory(dir);

            // connection string to our database
            var connectionString = Dts.Variables["ConnectionString"].Value.ToString();

            // the sql command to execute
            var sql = Dts.Variables["PriceHistorySqlCommand"].Value.ToString();

            var list = new List<Hash>();
            using (var cnn = new SqlConnection(connectionString))
            {
                cnn.Open();
                using (var cmd = new SqlCommand(sql, cnn))
                {
                    cmd.CommandTimeout = 0;
                    var dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        // store result in temporary hash
                        var h = new Hash();
                        h["cusip"] = dr["cusip"].ToString();
                        h["symbol"] = dr["symbol"].ToString();
                        h["product_id"] = dr["product_id"].ToString();
                        h["last_price_dt_id"] = dr["last_price_dt_id"].ToString();

                        list.Add(h);

                        // process batches of 100 at a time 
                        // (This requires System.Threading.dll (CTP of parallel extensions) to be installed in the GAC)
                        if (list.Count >= 100)
                        {
                            System.Threading.Tasks.Parallel.ForEach(list, item =>
                            {
                                var dt = item["last_price_dt_id"].TryGetDateFromDateDimensionId(end.AddYears(-100));
                                DownloadPriceHistory(item["product_id"], item["cusip"], item["symbol"], dt);
                            });
                            list.Clear();
                        }
                    }
                }
            }


            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        static void DownloadPriceHistory(string id, string cusip, string symbol, DateTime begin)
        {
            // get write path
            var path = System.IO.Path.Combine(dir, cusip + ".csv");
            var url = String.Format("http://ichart.finance.yahoo.com/table.csv?s={0}&d={1}&e={2}&f={3}&g=d&a={4}&b={5}&c={6}&ignore=.csv",
                symbol.ToUpper(),
                (end.Month - 1).ToString("00"), end.Day.ToString("00"), end.Year,
                (begin.Month - 1).ToString("00"), begin.Day.ToString("00"), begin.Year);
            string csv;

            using (WebClient web = new WebClient())
            {
                try
                {
                    var text = web.DownloadString(url);
                    var lines = text.Split('\n');
                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
                    int i = 0;
                    foreach (var line in lines)
                    {
                        // skip first line its a header
                        if (i == 0)
                            sb.AppendLine(CSV_FORMAT);
                        // ensure line being added is not null
                        else if (false == String.IsNullOrEmpty(line) && false == String.IsNullOrEmpty(line.Trim()))
                            sb.AppendLine(id + "," + cusip + "," + line);
                        i++;
                    }
                    // add header and body
                    csv = sb.ToString();
                }
                catch (System.Net.WebException)
                {
                    // 404 error
                    csv = CSV_FORMAT;
                }
            }

            System.IO.File.WriteAllText(path, csv);
        }
    }

    /// <summary>
    /// Some simple extension methods.
    /// </summary>
    public static class ExtensionMethods
    {
        /// <summary>
        /// Gets a datetime object from a dimension id string for example '20090130' would be translated to
        /// a proper datetime of '01-30-2009 00:00:00'. If the string is empty than we default to the passed
        /// in <paramref name="defaultIfNull"/>.
        /// </summary>
        /// <param name="str">The string</param>
        /// <param name="defaultIfNull">The default null.</param>
        /// <returns>Returns the datetime.</returns>
        public static DateTime TryGetDateFromDateDimensionId(this string str, DateTime defaultIfNull)
        {
            if (String.IsNullOrEmpty(str)) return defaultIfNull;
            return DateTime.Parse(str.Substring(4, 2) + "/" + str.Substring(6, 2) + "/" + str.Substring(0, 4));
        }
    }
}

Answer 1

使用SSIS从Yahoo Finance Chart网站导入报价历史价格：

还有一种使用SSIS将Yahoot Chart网站的股票代码价格历史记录导入数据库的方法。这是使用SSIS 2008 R2和SQL Server 2008 R2中的数据库编写的示例软件包

使用SO_14797886.dtsx创建一个名为（例如Business Intelligence Development Studio (BIDS)）的SSIS包，并创建一个连接到数据库的OLE DB连接管理器/数据源。此示例使用数据源OLEDB_Sora.ds连接到运行实例Sora的本地计算机上的数据库KIWI\SQLSERVER2008R2。 KIWI是计算机名称，SQLSERVER2008R2是实例名称。

在数据库中执行以下给定脚本以创建两个表。


表dbo.TickerSymbols将保存有关股票代码列表以及您要导入价格文件的开始和结束日期的信息，以及导入的分辨率。分辨率可以包含d或day之类的值； w表示weekly; m表示monthly;和y表示yearly。
表dbo.TickerPriceHistory将保存从Yahoo Finance Chart网站下载的交易品种的价格历史信息。
插入脚本已为股票代码AAPL（Apple）添加了四个记录； MSFT（Microsoft）; GOOG（Google）;和YHOO（Yahoo）。每个记录都设置有不同的日期范围和分辨率。


创建表并插入少量股票代号的脚本：

CREATE TABLE dbo.TickerSymbols
(
        Id int IDENTITY(1,1) NOT NULL
    ,   Symbol varchar(10) NOT NULL
    ,   StartDate datetime NOT NULL
    ,   EndDate datetime NOT NULL
    ,   Resolution char(1) NOT NULL
    ,   CONSTRAINT [PK_TickerSymbols] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

CREATE TABLE dbo.TickerPriceHistory
(
        Id int IDENTITY(1,1) NOT NULL
    ,   Symbol varchar(10) NOT NULL
    ,   PriceDate datetime NOT NULL
    ,   PriceOpen numeric(18,2) NULL
    ,   PriceHigh numeric(18,2) NULL
    ,   PriceLow numeric(18,2) NULL
    ,   PriceClose numeric(18,2) NULL
    ,   Volume bigint NULL
    ,   AdjustmentClose numeric(18,2) NULL
    ,   CONSTRAINT [PK_TickerPriceHistory] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

INSERT INTO dbo.TickerSymbols (Symbol, StartDate, EndDate, Resolution) VALUES
        ('AAPL', '2012-02-01', '2012-02-04', 'd')
    ,   ('GOOG', '2013-01-01', '2013-01-31', 'w')
    ,   ('MSFT', '2012-09-01', '2012-11-30', 'm')
    ,   ('YHOO', '2012-01-01', '2012-12-31', 'y')
    ;
GO

在SSIS包上，创建以下变量。


EndDate：软件包将使用数据类型为 DateTime的此变量将在记录集列表中保留正在循环通过的符号的结束日期。
FileExtension：数据类型为 String的此变量将保存文件扩展名，以用于下载的文件。这是可选的。
FileName：数据类型为 String的此变量将保存给定符号的文件名。该名称是根据时间戳生成的，以避免覆盖以前下载的文件。单击变量，然后按F4查看属性。将属性 EvaluateAsExpression更改为 True。在“表达式”上单击“省略号”按钮以打开“表达式生成器”。将 Expression设置为以下值。该表达式的计算结果类似于 MSFT_20130210_092519.csv，其中MSFT是符号，其余信息是格式为 yyyMMdd_hhmmss的包开始时间，而 .csv是文件扩展名。



  @ [User :: Symbol] +“ _” +（DT_WSTR，4）YEAR（@ [System :: StartTime]）+ RIGHT（“ 00” +（DT_WSTR，2）MONTH（@ [System :: StartTime]）， 2）+ RIGHT（“ 00” +（DT_WSTR，2）DAY（@ [System :: StartTime]），2）+“ _” + RIGHT（“ 00” +（DT_WSTR，2）DATEPART（“ hh”，@ [System :: StartTime]），2）+ RIGHT（“ 00” +（DT_WSTR，2）DATEPART（“ mi”，@ [System :: StartTime]），2）+ RIGHT（“ 00” +（DT_WSTR，2 ）DATEPART（“ ss”，@ [System :: StartTime]），2）+ @ [User :: FileExtension]



FilePath：数据类型为 String的此变量将保存给定符号的下载文件的完整路径。单击变量，然后按F4查看属性。将属性 EvaluateAsExpression更改为 True。在“表达式”上单击“省略号”按钮以打开“表达式生成器”。将 Expression设置为值 @[User::RootFolder] + "\\" + @[User::FileName]。我们将使用此快递
Resolution：程序包将使用数据类型为 String的此变量将要循环通过的符号的重解信息保留在记录集列表中。
RootFolder：数据类型为 String的此变量将保存文件应下载到的根文件夹。
SQL_GetSymbols：数据类型为 String的此变量将包含T-SQL查询，以从数据库中获取股票代码信息。将值设置为 SELECT Symbol, StartDate, EndDate, Resolution FROM dbo.TickerSymbols
StartDate：程序包将使用数据类型为 DateTime的此变量在记录集列表中保存循环通过的符号的开始日期。
Symbol：包将使用数据类型为 String的此变量来保存股票代码，因为它在记录集列表中的每个记录之间循环。
SymbolsList：程序包将使用数据类型为 Object的此变量来保存数据库中存储的股票代号的结果集。
URLYahooChart：此数据类型为 String的变量将使用占位符保存指向Yahoo Finance Chart网站的URL，以为查询字符串填写适当的值。将值设置为 http://ichart.finance.yahoo.com/table.csv?s={0}&a={1}&b={2}&c={3}&d={4}&e={5}&f={6}&g={7}&ignore=.csv




在程序包上，右键单击“连接管理器”选项卡，然后单击 Flat File Connection...



在平面文件连接管理器编辑器的 General页上，执行以下操作：


将名称设置为 FILE_TickerPriceHistory
将描述设置为 Read the ticker symbol price history.
如果您已经有示例文件，请指向文件位置。 SSIS将根据文件中的数据推断设置。在这种情况下，我已经通过导航URL http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=9&b=1&c=2012&d=11&e=30&f=2012&g=m&ignore=.csv下载了文件，并将其保存为名称 C:\Siva\StackOverflow\Files\14797886\Data\\MSFT_20130210_092519.csv。
确保格式设置为 Delimited。
确保“标题行分隔符”设置为 {CR}{LF}
选中 Column names in the first data row框
点击列页面




在平面文件连接管理器编辑器的 Columns页面上，确保将行定界符设置为 {LF}，将列定界符设置为 Comma {,}。单击高级页面。



在平面文件连接管理器编辑器的 Advanced页上，将基于文件信息创建列。如下所示更改值，以使列名与数据库中的名称匹配。这样，列映射将更容易。除最后一列外的所有列都应将ColumnDelimiter设置为 Comma {,}。 Lsst列应将ColumnDelimiter设置为 {LF}。

Column              Data type                            DataPrecision DataScale
------------------- ------------------------------------ ------------- ---------
PriceDate           date [DT_DATE]
PriceOpen           numeric [DT_NUMERIC]                      18          2
PriceHigh           numeric [DT_NUMERIC]                      18          2
PriceLow            numeric [DT_NUMERIC]                      18          2
PriceClose          numeric [DT_NUMERIC]                      18          2
Volume              eight-byte unsigned integer [DT_UI8]      
AdjustmentClose     numeric [DT_NUMERIC]                      18          2

现在，您应该在包的底部看到两个连接管理器。



将 Execute SQL Task拖放到“控制流”选项卡上，然后在“常规”选项卡上执行以下操作。


将名称设置为 Get symbols from database
将描述设置为 Fetch the list of symbols and its download settings from database.
将ResultSet设置为 Full result set，因为查询将返回记录集。
将ConnectionType设置为 OLE DB
将连接设置为 OLEDB_Sora
从SQLSourceType选择 Variable
从SourceVariable中选择 User::SQL_GetSymbols
单击结果集页面。




在“执行SQL任务”的“结果集”页面上，单击“添加”，然后将“结果名称”设置为 0，以指示结果集的索引。从变量名中选择 User::SymbolsList，将结果集存储到对象变量中。



将Foreach循环容器拖放到Execute SQL Task之后。将执行SQL任务绿色箭头连接到Foreach循环容器。双击Foreach循环容器以查看Foreach循环编辑器。如下所示配置ForEach循环编辑器。



在ForEach循环编辑器的“变量映射”页面上，如下所示进行配置：



将 Script Task拖放到ForEach循环容器中。双击脚本任务以打开脚本任务编辑器。在“脚本任务”编辑器的“脚本”页面上，单击 ReadOnlyVariables上的省略号按钮，然后选择下面列出的变量。我们需要在脚本任务代码中使用它们。


用户：: EndDate
用户：： FileExtension
用户：： FileName
用户：： FilePath
用户：:分辨率
用户：： RootFolder
用户：: StartDate
用户：: Symbol
用户：： URLYahooChart




单击脚本任务编辑器上的 Edit Script...按钮，然后键入以下代码。输入代码后，关闭脚本任务编辑器。

C＃中的脚本任务代码：

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

namespace ST_5fa66fe26d20480e8e3258a8fbd16683.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()
        {
            try
            {
                string symbol = Dts.Variables["User::Symbol"].Value.ToString();
                DateTime startDate = Convert.ToDateTime(Dts.Variables["User::StartDate"].Value);
                DateTime endDate = Convert.ToDateTime(Dts.Variables["User::EndDate"].Value);
                string resolution = Dts.Variables["User::Resolution"].Value.ToString();
                string urlYahooChart = Dts.Variables["User::URLYahooChart"].Value.ToString();

                string rootFolder = Dts.Variables["User::RootFolder"].Value.ToString();;
                string fileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
                string fileName = Dts.Variables["User::FileName"].Value.ToString();
                string downloadPath = Dts.Variables["User::FilePath"].Value.ToString();

                if (!System.IO.Directory.Exists(rootFolder))
                    System.IO.Directory.CreateDirectory(rootFolder);

                urlYahooChart = string.Format(urlYahooChart
                                        , symbol
                                        , startDate.Month
                                        , startDate.Day
                                        , startDate.Year
                                        , endDate.Month
                                        , endDate.Day
                                        , endDate.Year
                                        , resolution);

                bool refire = false;
                Dts.Events.FireInformation(0, string.Format("Download URL of {0}", symbol), urlYahooChart, string.Empty, 0, ref refire);

                WebClient webClient = new WebClient();
                webClient.DownloadFile(urlYahooChart, downloadPath);

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Download error", ex.ToString(), string.Empty, 0);
            }
        }
    }
}

在脚本任务之后，将 Data Flow Task拖放到Foreach循环容器中。将绿色箭头从脚本任务连接到数据流任务。 “控制流”选项卡应如下图所示。



在“数据流任务”上，拖放“平面文件源”并按如下所示进行配置，以读取价格历史记录CSV文件。





拖放 Derived Column Transformation并使用表达式 Symbol创建一个名为 (DT_STR,10,1252)@[User::Symbol]的新列，以将符号添加到数据管道中。



拖放OLE DB Destination并按如下所示进行配置，以将数据插入数据库。





您的“数据流”选项卡应如下图所示：



在运行软件包之前，我们需要进行一些更改，以防止由于文件夹中没有文件而在设计时视图上出现任何警告或错误。

单击平面文件连接管理器 FILE_TickerPriceHistory，然后按 F4查看属性。将属性 DelayValidation更改为 True。这将确保在运行时对文件存在进行验证。单击表达式上的省略号按钮，并将 ConnectionString属性设置为值 @[User::FilePath]。从网站下载每个文件时，这将更改文件路径。



单击数据流任务，然后按 F4查看属性。将属性 DelayValidation更改为 True。这将确保在运行时对文件存在进行验证。



导航到“数据流”选项卡，然后单击“平面文件源”，然后按 F4查看属性。将属性 ValidateExternalMetadata更改为 False。这将确保在运行时对平面文件是否存在进行验证。



让我们导航到文件夹 C:\Siva\StackOverflow\Files\14797886，将保存下载文件的文件夹为空。该文件夹不必为空。这仅用于执行检查。



针对数据库运行以下SQL语句，以验证表中的数据。第二个表应该为空。

SELECT * FROM dbo.TickerSymbols;
SELECT * FROM dbo.TickerPriceHistory;

执行包。如果一切设置正确，则该软件包应成功运行并下载表 dbo.TickerSymbols中列出的每个符号的文件





文件应成功保存到文件夹 C:\Siva\StackOverflow\Files\14797886。请注意，每个文件都是根据包中提供的表达式进行适当命名的。



针对数据库运行以下SQL语句以验证表中的数据。 dbo.TickerPriceHistory表现在应具有从网站下载的价格文件中的数据。

SELECT * FROM dbo.TickerPriceHistory;

上面的样本包说明了如何从Yahoo Finance Chart网站下载价格文件的给定股票代码列表，并将其加载到数据库中。