SSIS中关于变量的使用说明

 将项目中用到的一个方法记录下

在ssis中，我们要将oracle的库通过ssis转储到sqlserver中，在oracle环境下的sql语句中有用到一个sqlserver库的动态值，所以就要用到变量。现将过程记录下，以备后用

1.首先定义两个变量，一个是存储原来sql的变量，也就是sql中带有变量，注意变量书写为 ：xxx的形式，而且sql中不能有换行符等特殊符号，我这里的是

select ID, SiteID, StoreName, FolioNo, CCNo, Description, ItemRemark, Unit, WAC,BinLocation, LeadTime, MinStock, LogUser, LastUpdated, Status, Category, Remarks, CreateDate, LastIssueDate, LastTOCDate, LastPurchaseCost, StockBal, LastStockCheckDate, ForecastQty, Consumption, Consumption1, Consumption2, Consumption3, ConsumMonth, ConsumMonth1, ConsumMonth2, ConsumMonth3, Actions, LastPurchaseDate, BUDate, AvgMonthCon, OpernBalance, CloseBalance, MinStock+(AvgMonthCon*LeadTime) as ReOrderLevel, MonthConsumption, LastWAC, UNSPSC, 'SYS' as UIDLSTUPD, 'GDMX' as TRANIDLSTUPD, sysdate as DTMISLSTUPD from (select ID, SiteID, StoreName, FolioNo, CCNo, Description, ItemRemark, Unit, WAC, BinLocation, LeadTime, MinStock, LogUser, LastUpdated, Status, Category, Remarks, CreateDate, LastIssueDate, LastTOCDate, LastPurchaseCost, StockBal, LastStockCheckDate, ForecastQty, Consumption, Consumption1, Consumption2, Consumption3, ConsumMonth, ConsumMonth1, ConsumMonth2, ConsumMonth3, Actions, LastPurchaseDate, BUDate, case when (Consumption + Consumption1 + Consumption2 + Consumption3) <> 0 and (ConsumMonth + ConsumMonth1 + ConsumMonth2 + ConsumMonth3) <> 0 then ceil((Consumption + Consumption1 + Consumption2 + Consumption3/ConsumMonth + ConsumMonth1 + ConsumMonth2 + ConsumMonth3)*1000000)/1000000 else 0 end as AvgMonthCon, OpernBalance, CloseBalance, ReOrderLevel, MonthConsumption, LastWAC, UNSPSC from (select inve.itemnum as ID, null as SiteID, inve.location as StoreName, inve.itemnum as FolioNo, item.ccnum as CCNo, item.description as Description, null as ItemRemark, item.issueunit as Unit, invc.avgcost as WAC, inve.binnum as BinLocation, inve.deliverytime as LeadTime, inve.sstock as MinStock, null as LogUser, null as LastUpdated, item.status as Status, null as Category, item.remark as Remarks, item.createdate as CreateDate, inve.lastissuedate as LastIssueDate, null as LastTOCDate, invc.lastcost as LastPurchaseCost, invb.curbal as StockBal, invb.physcntdate as LastStockCheckDate, inve.forecastqty as ForecastQty, abs(invyc.qty) as Consumption, abs(invyc1.qty) as Consumption1, abs(invyc2.qty) as Consumption2, abs(invyc3.qty) as Consumption3, case when item.createdate is null then 0 else to_number(to_char(:batchRunTime, 'mm')) - 1 end as ConsumMonth, case when item.createdate is null or to_number(to_char(item.createdate, 'yyyy')) > to_number(to_char(:batchRunTime, 'yyyy')) - 1 then 0 when to_number(to_char(item.createdate, 'yyyy')) = to_number(to_char(:batchRunTime, 'yyyy')) - 1 then 12 - to_number(to_char(item.createdate, 'mm')) when to_number(to_char(item.createdate, 'yyyy')) < to_number(to_char(:batchRunTime, 'yyyy')) - 1 then 12 else null end as ConsumMonth1, case when item.createdate is null or to_number(to_char(item.createdate, 'yyyy')) > to_number(to_char(:batchRunTime, 'yyyy')) - 2 then 0 when to_number(to_char(item.createdate, 'yyyy')) = to_number(to_char(:batchRunTime, 'yyyy')) - 2 then 12 - to_number(to_char(item.createdate, 'mm')) when to_number(to_char(item.createdate, 'yyyy')) < to_number(to_char(:batchRunTime, 'yyyy')) - 2 then 12 else null end as ConsumMonth2, case when item.createdate is null or to_number(to_char(item.createdate, 'yyyy')) > to_number(to_char(:batchRunTime, 'yyyy')) - 3 then 0 when to_number(to_char(item.createdate, 'yyyy')) = to_number(to_char(:batchRunTime, 'yyyy')) - 3 then 12 - to_number(to_char(item.createdate, 'mm')) when to_number(to_char(item.createdate, 'yyyy')) < to_number(to_char(:batchRunTime, 'yyyy')) - 3 then 12 else null end as ConsumMonth3, null as Actions, null as LastPurchaseDate, null as BUDate, null as AvgMonthCon, null as OpernBalance, null as CloseBalance, null as ReOrderLevel, null as MonthConsumption, null as LastWAC, null as UNSPSC from inventory inve left join item on item.itemnum = inve.itemnum left join invcost invc on invc.itemnum = inve.itemnum and invc.siteid = inve.siteid and invc.location = inve.location left join (select itemnum, location, siteid, max(physcntdate) physcntdate, sum(curbal) curbal from invbalances group by itemnum, location, siteid) invb on invb.itemnum = inve.itemnum and invb.siteid = inve.siteid and invb.location = inve.location left join (select itemnum, storeloc, siteid, sum(quantity) qty from matusetrans where issuetype = 'ISSUE' and to_char(actualdate, 'yyyy') = to_char(:batchRunTime, 'yyyy') group by itemnum, storeloc, siteid) invyc on invyc.itemnum = inve.itemnum and invyc.storeloc = inve.location and invyc.siteid = inve.siteid left join (select itemnum, storeloc, siteid, sum(quantity) qty from matusetrans where issuetype = 'ISSUE' and to_char(actualdate, 'yyyy') = to_char(to_number(to_char(:batchRunTime, 'yyyy')) - 1) group by itemnum, storeloc, siteid) invyc1 on invyc1.itemnum = inve.itemnum and invyc1.storeloc = inve.location and invyc1.siteid = inve.siteid left join (select itemnum, storeloc, siteid, sum(quantity) qty from matusetrans where issuetype = 'ISSUE' and to_char(actualdate, 'yyyy') = to_char(to_number(to_char(:batchRunTime, 'yyyy')) - 2) group by itemnum, storeloc, siteid) invyc2 on invyc2.itemnum = inve.itemnum and invyc2.storeloc = inve.location and invyc2.siteid = inve.siteid left join (select itemnum, storeloc, siteid, sum(quantity) qty from matusetrans where issuetype = 'ISSUE' and to_char(actualdate, 'yyyy') = to_char(to_number(to_char(:batchRunTime, 'yyyy')) - 3) group by itemnum, storeloc, siteid) invyc3 on invyc3.itemnum = inve.itemnum and invyc3.storeloc = inve.location and invyc3.siteid = inve.siteid))

 

一个是用来从sqlserver中查出的动态值，其值为空

2.从toolbox中拖出一个 Execute SQL Task，按照下图编辑

图2

3.从toolbox中拖出一个script task 编辑

 进行编辑

 MsgBox(Dts.Variables("timestamp").Value)
        Dts.Variables("execSQL").Value = Dts.Variables("execSQL").Value.ToString.Replace(":batchRunTime", "to_date('" & Dts.Variables("timestamp").Value.ToString & "','yyyy/mm/dd HH24:MI:SS')")
        'MsgBox(Dts.Variables("execSQL").Value.ToString)
        ' Dim sw As StreamWriter = New StreamWriter("d:\vb.txt", False)
        'sw.WriteLine(Dts.Variables("execSQL").Value.ToString)
        ' sw.Flush()
        ' sw.Close()
        Dts.TaskResult = Dts.Results.Success

 

4.在data flow中选择最终修改后的sql变量

 到此这个工作就完成了。。。

下来还有一种方案，就是在script task中直接连接数据库，这样就只需要定义一个变量，

Public Sub Main()
        Dim sConnectionString As String

        sConnectionString = "Provider=SQLOLEDB.1;server=机器名;database=数据库名;uid=sa;pwd=密码"
        Dim myConnection As New OleDb.OleDbConnection(sConnectionString)
        Dim sSQL As String
        sSQL = "select left(LSYSRVAL.PARMVAL,10) from LSYSRVAL where PARMNAME = 'GLDMAXTOMISLBDTMP'"
        Dim myCommand As New OleDb.OleDbCommand(sSQL, myConnection)
        Dim result As String
        myConnection.Open()
        result = CStr(myCommand.ExecuteScalar)
        result = "to_date('" & result & "','yyyy-mm-dd')"
        MsgBox(result)
        Dts.Variables("STOCKMSTSQL").Value = Dts.Variables("STOCKMSTSQL").Value.ToString.Replace("sysdate", result)
        myConnection.Close()
        Dim sw As StreamWriter = New StreamWriter("d:\DataView.txt", False)
        sw.WriteLine(Dts.Variables("STOCKMSTSQL").Value.ToString)
        sw.Flush()
        sw.Close()
		Dts.TaskResult = Dts.Results.Success
	End Sub

 

ok，完成