SSIS Package Deployment Tip

1. Can modify connection string with password directly on connection manager(no need to change .dtsconfig file after deployment)

2. Steps: copy all deployment folder including correct  package config file to target server and run deployment

SSIS Package Deployment

• Creating a SSIS Package
• Using Packing configuration:
• SSIS->Package Configuration->select connection string for db connection
• Build package
• In the deployment folder, modify *.dtsconfig with password like the following

Data Source=colvts02;uid=configurator;pwd=configurator;Initial Catalog=ST_Support;

• Copy deployment folder to target server
• Double click *.deployment file , during installation , specified a installation folder with writing permission (without enough permission, might get error that the config file access is denied)

SQL Query Dynamic Select Sample

DECLARE @Country varchar(10)

02
DECLARE @City varchar(10)

03
SET @Country = ''

04
SET @City = 'ALL'

05

06
SELECT * FROM dbo.Customers

07
--判斷如果是空值傳入則直接變成本身欄位比對 若不是空 則用傳進來的變數比對

08
--一般空值你可以應用在 TextBox上

09
WHERE Country = CASE @Country WHEN '' THEN Country ELSE @Country END

10
--那如果你還有一個DropDownList 你可以把預設選項做一個設定值

11
--例如說DropDownList預設值是 ALL

12
--判斷如果是ALL傳入則直接變成本身欄位比對 若不是ALL 則用傳進來的變數比對

13
AND City = CASE @City WHEN 'ALL' THEN City ELSE @City END

Calling Web Service from SQL Server 2005

 

(1) Create a web service ( .net framework 2.0, can not be higher) and deploy to server

link : http://colweb01/test/ConfiguratorWSNF2/Service.asmx

 

(2)  Create a SQL Server Project (net framework 2.0)

(3) Add web reference with above link

(4) Create a table-value function

code:

// Service1 is web service client class

#region Table-Value Function
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "GetOrderList_FillRow", TableDefinition = "Orderno NVARCHAR(50),AttHost NVARCHAR(10), Model NVARCHAR(10),MastType NVARCHAR(10),ElineonDate NVARCHAR(20),AlineonDate NVARCHAR(20),TFC NVARCHAR(10),WholeSaleCode NVARCHAR(100),WholeOPtions NVARCHAR(250)")]
public static IEnumerable GetOrderList()
{
     return new Service1().GetAS400OrderInfo();
}

public static void GetOrderList_FillRow(
     object OrderObj,
     out SqlString Orderno,
     out SqlString AttHost,
     out SqlString Model,
     out SqlString MastType,
     out SqlString ElineonDate,
     out SqlString AlineonDate,
     out SqlString TFC,
     out SqlString WholeSaleCode,
     out SqlString WholeOPtions
     )
{
     AS400A004PEntity order = (AS400A004PEntity)OrderObj;
     //   ProductID = new SqlInt32(Convert.ToInt32(r["ProductID"].ToString()));
     //  Name = new SqlString(r["Name"].ToString());
     Orderno = new SqlString(order.ADODN);
     AttHost = new SqlString(order.ADATT);
     Model = new SqlString(order.ADMDN);
     MastType = new SqlString(order.ADMST);
     ElineonDate = new SqlString(order.ADEON);
     AlineonDate = new SqlString(order.ADAON);
     TFC = new SqlString(order.TFC);
     WholeSaleCode = new SqlString(order.WholeSalesCode);
     WholeOPtions = new SqlString(order.WholeOptions);

}

#endregion

(5) build component , copy dll file to sql server local folder( reason:since need to call web service which is external access source, have to do manual deployment).

(6)deployment .sql ( need to deploy assembly to external-access security level and xmlserizlier , sgen.exe)

-------------------- clean up

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ConfiguratorServiceNF2')
   DROP FUNCTION ConfiguratorServiceNF2
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetFirstAS400')
   DROP FUNCTION GetFirstAS400
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SDY')
   DROP FUNCTION SDY
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetOrderList')
   DROP FUNCTION GetOrderList
go
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'XmlSerializers')
    DROP ASSEMBLY [XmlSerializers]
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SQLServerConfiguratorNF2')
   DROP ASSEMBLY SQLServerConfiguratorNF2
GO
----------------------------- create sql server assembly
Create ASSEMBLY SQLServerConfiguratorNF2 FROM 'c:\vss\test\SQLServerConfiguratorNF2.dll'
WITH PERMISSION_SET = External_Access

CREATE ASSEMBLY [XmlSerializers] from
'c:\vss\test\SQLServerConfiguratorNF2.XmlSerializers.dll'
WITH permission_set = SAFE
GO

------------------------------- create function
CREATE FUNCTION [dbo].[GetOrderList]()
RETURNS  TABLE (
    [Orderno] [nvarchar](50) NULL,
    [AttHost] [nvarchar](10) NULL,
    [Model] [nvarchar](10) NULL,
    [MastType] [nvarchar](10) NULL,
    [ElineonDate] [nvarchar](20) NULL,
    [AlineonDate] [nvarchar](20) NULL,
    [TFC] [nvarchar](10) NULL,
    [WholeSaleCode] [nvarchar](100) NULL,
    [WholeOPtions] [nvarchar](250) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerConfiguratorNF2].[UserDefinedFunctions].[GetOrderList]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'ConfiguratorServiceNF2.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=26 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

--------------------------------- exceute
SELECT * FROM [ST_SUPPORT].[dbo].[GetOrderList] ()
GO

BulkUpdate With DAAB

 

Sample code:

 

private static Database db = DatabaseFactory.CreateDatabase("VTS01ConnectionString");

       public static void TestBulkUpate()
       {
           // Read all the customers in a dataset
           string selectSql = "SELECT  * FROM FormC_Transfer";
           DataSet customerDataSet = db.ExecuteDataSet(CommandType.Text, selectSql);

           // Modify Records
           foreach (DataRow dr in customerDataSet.Tables[0].Rows)
               dr["CategoryAddress"] += "_sdy ";

           // Create UpdateCommand
           string updateSql = "UPDATE FormC_Transfer SET CategoryAddress = @CategoryAddress WHERE CitemId=@CitemId";
           DbCommand updateCommand = db.GetSqlStringCommand(updateSql);
           db.AddInParameter(updateCommand, "CitemId", DbType.Int32, "CitemId", DataRowVersion.Current);
           db.AddInParameter(updateCommand, "CategoryAddress",
               DbType.String, "CategoryAddress", DataRowVersion.Current);

           // Execute Batch Update
           // Use zero to specify database
           // maximum batch update size
          // db.UpdateDataSet(customerDataSet, customerDataSet.Tables[0].TableName, null, updateCommand, null, UpdateBehavior.Standard);

 

db.UpdateDataSet(customerDataSet, customerDataSet.Tables[0].TableName, null, updateCommand, null, UpdateBehavior.Transactional,0);

Jquery Call Server Control

 

Server Control

 

<input type="text" id="datepicker" align="middle" style="width: 330px"  runat="server"  >

 

 

In Jquery:

Query Selector有一个十分方便的方法可以调用到我们的控件，那就是ID Selector，如下：

$("[id$=lblMessage]")