SSIS script task中用代码的方式查询数据库样例

最新推荐文章于 2021-05-09 16:32:05 发布

原创最新推荐文章于 2021-05-09 16:32:05 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

Sql Server 同时被 3 个专栏收录

125 篇文章

订阅专栏

VB.NET

31 篇文章

订阅专栏

商业智能

20 篇文章

订阅专栏

本文展示了一个使用 C# 在 SQL Server 中执行特定查询来获取文件名并进行格式化的示例。通过设置数据库连接字符串，打开连接，并执行一个 SQL 查询来获取带有日期格式的文件名。

            Dim sqlconstr As String = "Data Source=" + Dts.Variables("SQL01").Value.ToString() + ";Initial Catalog=EDW_PreSource;Integrated Security=True"
            Dim conn As SqlConnection = New SqlConnection(sqlconstr)
            Try
                conn.Open()
                Dim sSql As String = "select top 1 substring(dw_file_name,1,len(dw_file_name)-4) + '_' +  replace(replace(replace(CONVERT(varchar, dw_insert_date, 120 ),'-',''),' ',''),':','') + '.txt' as DW_FILE_NAME2" + _
                                    " FROM [EDW_PreSource].[dbo].[WELLCARE_SERVICEFUND] order by DW_INSERT_DATE desc"
                Dim cmd As SqlCommand = New SqlCommand(sSql, conn)
                Dts.Variables("FileShortName_ServiceFund").Value = Convert.ToString(cmd.ExecuteScalar())
                Dts.TaskResult = Convert.ToInt32(ScriptResults.Success)
            Catch
                Dts.TaskResult = Convert.ToInt32(ScriptResults.Failure)
            Finally
                conn.Close()
            End Try 



   Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum