.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Deploying SSIS Package configurations in production and development

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

I'm working on a SSIS package which I want to deploy onto our development environment on a daily basis as I work on it. In addition, I want it to be easy to deploy to production. We're using xml configuration files for our packages, but I haven't seen anyone discuss the entire development process.

Do people keep their production and development configuration files in source control? If so, how? Are they part of your BIDS SSIS project? I tried adding them to "Miscellaneous". i.e. Example:

I have a project with the following SSIS Packages in it:




In the Miscellaneous folder I have:






I did this so I can keep them in source control. We don't use package deployment utilities, we just deploy by hand, but putting the files in Miscellaneous breaks the building of the deployment utilities because it attempts to copy the config files into the Deployment directory twice.


In addition, I set up the package configurations such that the

View Complete Post

More Related Resource Links

Deploying taxonomy term store and managed meta column from development machine to production machine

Hi, In my development environment, I created a taxonomy term store by importing a CSV file. I also created a site column of type "managed meta data" based on that term store. Now, I want to migrate to production environment. What is the best way to do this? When I generate the schema of my site column, it looks like this: <Field Type="TaxonomyFieldTypeMulti"    DisplayName="Article Type"    List="{d8b20e4f-ce12-4def-b59d-d44da2c13234}"    WebId="eef74e7c-a852-4c89-b811-e31b86853641"    ShowField="Path1033"    Required="FALSE"    EnforceUniqueValues="FALSE"    Group="Custom Columns"    ID="{47f78411-65fa-43c9-8cb8-68955aea1256}"    SourceID="{eef74e7c-a852-4c89-b811-e31b86853641}"    StaticName="Article_x0020_Type"    Name="Article_x0020_Type"    Version="4"    Mult="TRUE"    Sortable="FALSE"> <Default></Default> <Customization> <ArrayOfProperty> <Property> <Name>SspId</Name> <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" p4:type="q1:string" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">99d

Which edition of sql server for SSIS package development



which is the minimum edition of sql server which will allow me to develop ssis in BIDS and execute them.

I use to pull data from oracle by means of some simple transactions and populate them in Sql Server

Deploying Publishing SharePoint site from development to test and production environment.




I have created a Publishing Site in SharePoint 2010, with pages created from custom page layouts, content types and Publish Content areas which I have created using SharePoint Designer 2010.

Also, I created custom Lists, Libraries and workflows in my development environment.

I want to replicate the site in test/production environment.

Can you please suggest me the best way to package all, along with custom site settings from development environment so that I can migrate small changes to test/ production environment later also without creating .wsp as I have access to SharePoint designer 2010 only.

Abhijit Sil

One SSIS package - multiple configurations?

I'm looking for a basic guideline/best practices for creating an SSIS setup involving one "master" package called with one variable that will link it to a set of configurations based on that variable. Currently we create a new package for each client, each with their own configuration. Now each package is basically the same but we create an entirely new package file for each customer. There are variables that are of the YES/NO variety to enable/disable specific functions based on client need. As it is now, if we improve the package (a new version if you will) we have to go back to all prior packages to update and make the adjustment. These can be anything from a small SQL code change to an entirely new container of tasks. Either way, you get the idea, this is very inefficient and I am attempting to standardize our packages and process. If anyone could post a few links or point me in the right direction it would be very much appreciated. Thank you!

App_Data - development vs production - SQLExpress


Help please, on mdf/ldf files manipulation between dev and prod App_Data folders.

My dev folder: ...\Visual Studio 2010\Projects\WebApplication1\
My prod folder: C:\inetpub\mywebsite\

There are 4 files in [dev]\APP_Data

The VS2010 publishing process copies the 4 files to [prod]\App_Data where my website users interact with them updating data therein.

Next time I publish, the files in [prod]\App_Data get overwritten, losing all current membership and app data changed by my website users.

I tried using SQL Server Management Studio, before publishing, to export current data from [prod]\App_Data\aspnetdb.mdf and myappdb.mdf, and import to [dev]\App_Data\aspnetdb.mdf and myappdb.mdf, but that brought up a bunch of detaching/attaching issues, ending up wiping out all security permissions settings of all the related files, even though I had used SQL Server Configuration Manager to stop the SQLExpress Service beforehand.

IIS Application Pool Identity:

App_Data mdf/ldf files security permission settings:
    SYSTEM: Full Control (dev/prod)
    NETWORK SERVICE: Read/Write (dev/prod)

using SharePoint List for SSIS package

I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

Deploying custom Web Parts on SharePoint 2007 production server

I would like to develop a custom SharePoint 2007 Web Part for a client. Currently I'm using Visual Studio to deploy my solution to a test environment (since it's a SharePoint Project I choose just Build -> Deploy from the menu). What are the appropriate steps to deploy it to the production server?

I'd like keep it simple and safe (i.e. use the principle of least privilege). Therefore (if possible) avoid using stsadm, achieve this through web interface, or avoid using administrative rights.

Also, can I specify the scope of my Web Part, i.e. choose on which Web Application or Site Collection will it be installed?

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

SSIS package Scheduled Job Not Running

I'm not sure which way to research this issue.. I have an SSIS package that we can run just fine manually. But, when we put it into a scheduled job, it won't run. The package is on the same system as the SQL Server (2005), but it does call for updates from tables on another SQL Server (2000) on another domain (trusted). So, I'm thinking this might be a permissions / security issue, but not sure where to start... The package ProtectionLevel is "don't save sensitive", but that didn't help. Any help would be appreciated. Thanks. jill

How to make SSIS Package Fail when something goes wrong

Hello, Is there any way I can make the package (SSIS 2005) fail when something happen? Like: if some value become zero I will to make the package fail from with in the SSIS, so that the AgentJOB  that started this package will know? Thanks, Prabhat Nath

Can not get excel connection when calling an SSIS package from ASP

I have an SSIS package importing data from excel. I have coded it and deployed it on the sql 2008 server. I then execute it via a stored procedure with the following: select @cmd = 'C:\"Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /sq "HRIS"' select   @cmd = @cmd + ' /SET \Package.Variables[User::path].Properties[Value];"' + @path + '"' exec   @rtn = master..xp_cmdshell @cmd   This works fine when  i execute it on my local machine. But when I try to call this Stored proc from my asp app (classic asp, not .net) I get the error: Code: 0xC0202009 Source: HRIS Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error   Any help would be greatly apprecitated

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

help regarding SSIS package!

Hi Everyone,, I am pretty much new to SSIS. Any help regarding this project would be appreciate it. I need to transfer data from the files into a Database. I need to use FTP to copy those files locally on a machine and then combine/merge those files  and then read the data from those files and copy the values into the database. I have atleast 5-6 files which I need to combine or read them individually to form a single row in the database. (I am not sure if merging into single file or reading as each indivial file is faster!) Those files are in report formats and can be opened like notepad. There is Ton of data in each file and some filtering is required before I insert a single record into the DB. I just started learing the SSIS package, i am not really sure how I can do it.Any help or sample code or screen shots would be really appreciated!! Thanks Sunny.

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

What does strategy exist to deploy SSIS package and my own data flow components into a enterparise s

I created a SSIS package and several data flow componenets for this package.    What does strategy exist to deploy SSIS package and data flow components into a enterparise server?   Thanks in advance.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend