locked
SSIS Team Development setup RRS feed

  • Question

  • Hello  All,

    What  is the best way to set up ssis  team development setup,we are planning to install client tools on the developer workstation,what should be the system configuration of these work stations in terms of memory and cpu should they be same as prod servers and the initial source data  should we directly connect to the source databases.

    Some sources are on the same domain and some sources outside the domain,for the access of the source on same domain  should we give read access to the developer windows  login or create a sqlserver login and for access to remote sources outside the domain what method of access we should use.We have two developers  who will be working on this project  and the we will be using TFS for team development.

    Many Thanks

    Kumar

    Sunday, July 1, 2012 12:38 PM

Answers

  • Dan,

    In respect to creating test data  for development  what things do we need to consider for creating data manually and what approach should we follow for load testing in terms of  test data .

    Many Thanks

    Kumar

    • Marked as answer by Vision2040 Monday, July 2, 2012 6:39 PM
    Sunday, July 1, 2012 5:23 PM

All replies

  • What  is the best way to set up ssis  team development setup,we are planning to install client tools on the developer workstation,what should be the system configuration of these work stations in terms of memory and cpu should they be same as prod servers and the initial source data  should we directly connect to the source databases.

    It's generally best to avoid using production databases for testing.  In addition to security and availability concerns, consider cases where schema changes under development haven't yet been promoted to production.  That will require a non-production database anyway.  SSIS developers in our environment have local SQL Server instances for package development.  They use package configurations to allow the same package to use different resources depending on the environment.

    It is seldom necessary, or even practical, to have development workstations mirror production server hardware.  Initial package development and unit testing doesn't need to be done using large datasets.  I suggest workstations be configured appropriately so that developers can run tests locally as possible and use a shared non-production environment (i.e. Development/QA/UAT) for subsequent integration and performance testing of production-like datasets.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 1, 2012 3:19 PM
  • Dan,

    In respect to creating test data  for development  what things do we need to consider for creating data manually and what approach should we follow for load testing in terms of  test data .

    Many Thanks

    Kumar

    • Marked as answer by Vision2040 Monday, July 2, 2012 6:39 PM
    Sunday, July 1, 2012 5:23 PM
  • To create data manually, I would start with development schema exactly like production and include system/application domain data (e.g. countries, products, stores).  You can then generate test data for tables like customers and orders using that data along with random values. 

    The script below illustrates how you can generate as much test data as you need.  This technique can also be extended to distribute data more closely to production ratios as needed.  In this example, I abut the same number of customers and orders per store.  You can also add test data to exercise test contitions that might not exist in production (e.g. order detail with invalid product codes).

    CREATE TABLE dbo.Country(
    	CountryCode char(2) NOT NULL
    	,CountryName varchar(100) NOT NULL
    	);
    INSERT INTO dbo.Country VALUES
    	('US', 'UNITED STATES')
    	,('UK', 'UNITED KINGDOM')
    	,('IN', 'INDIA')
    	,('HK', 'HONG KONG')
    	,('CN', 'CHINA');
    
    CREATE TABLE dbo.Product(
    	ProductCode char(5) NOT NULL
    	,ProductName varchar(100) NOT NULL
    	);
    INSERT INTO dbo.Product VALUES
    	('A123', 'Widget')
    	,('B234', 'Stuff')
    	,('C3456', 'Thing');
    
    CREATE TABLE dbo.Store(
    	StoreCode char(5) NOT NULL
    	,StoreName varchar(100) NOT NULL
    	,Address1 varchar(100) NOT NULL
    	,Address2 varchar(100) NULL
    	,City varchar(100) NOT NULL
    	,State char(2) NOT NULL
    	,CountryCode char(2) NOT NULL
    	,PostalCode varchar(100) NOT NULL
    	);
    INSERT INTO dbo.Store VALUES
    	('S0001', 'Store 1', '123 Main St.', NULL, 'Anytown', 'NY', 'US', '10211')
    	,('S0002', 'Store 2', '2345 Broadway Blvd.', NULL, 'Anytown', 'NE', 'US', '68197')
    	,('S0003', 'Store 3', '34567 Long Rd', NULL, 'Anytown', 'CA', 'US', '90001');
    
    CREATE TABLE dbo.Customer(
    	CustomerID int NOT NULL IDENTITY
    	,FirstName varchar(30) NOT NULL
    	,LastName varchar(30) NOT NULL
    	);
    INSERT INTO dbo.Customer
    SELECT FirstName, LastName
    FROM (VALUES('John'), ('Mary'), ('Kumar'), ('Dan')) AS first_names(FirstName)
    CROSS JOIN (VALUES('Jones'), ('Smith'), ('Obama'), ('Guzman')) AS last_names(LastName);
    
    CREATE TABLE dbo.OrderHeader(
    	OrderID int NOT NULL IDENTITY
    	,CustomerID int NOT NULL
    	,StoreCode char(5) NOT NULL
    	,OrderDate date NOT NULL
    	);
    WITH
    	T4 AS (SELECT 0 AS N FROM (VALUES(0),(0),(0),(0)) N(N)),
        T256 AS (SELECT 0 AS N FROM T4 AS A, T4 AS B, T4 AS C, T4 AS D),
        T16M AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM T256 AS A, T256 AS B, T256 AS C)
    INSERT INTO dbo.OrderHeader(CustomerID, StoreCode, OrderDate)
    SELECT CustomerID, StoreCode, DATEADD(day, N % 365, '20110101')
    FROM dbo.Customer
    CROSS JOIN dbo.Store
    CROSS JOIN T16M
    WHERE N < 1000;
    
    CREATE TABLE dbo.OrderDetail(
    	OrderID int NOT NULL
    	,ProductCode char(5) NOT NULL
    	,OrderQuantity int NOT NULL
    	);
    INSERT INTO dbo.OrderDetail(OrderID, ProductCode, OrderQuantity)
    SELECT OrderID, ProductCode, CAST(SUBSTRING(CAST(NEWID() AS varbinary(16)), 6, 1) AS int)
    FROM dbo.OrderHeader
    CROSS JOIN dbo.Product;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 1, 2012 7:52 PM