SSIS Deployment Question About Conn Strings? Please Help.
Jan 23, 2007
Hi everyone:
I have a package(SSIS) that works fine on a local SQL Server. Once I deploy it to the server A, and change the connection strings, it throws the following error:
Error: The AcquireConnection method call to the connection manager "DatabaseName" failed with error code xxxxx.
Error: component "OLE DB Destination"(16) failed validation and returned error code xxxx.
Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "Communication Link Failure"
Error: Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "TCP Provider: An existing connection was forcibly closed by the remote host."
Error: Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "Login failed for user 'username'."
I have confirmed from my Network Engineer that this is not a Network issue. I can connect to the the target sql server from SQL Server A(where the package is running). The only thing that has changed is the connection string. In my package I connect to a local database on the server, and a remote SQL server. The connection to the local sql server works fine from the package. The remote SQL Server connection string is not working, I think. I also noticed that after I enter my password in the conn string, within the VS 2005, after building it, and deploying it on the server, I check back the conn string, and it is no longer there. I have read some posts about it, and I understand that SSIS hides it. What is the work around here? I have never used config files for SSIS, so I would need exact instructions to do that, if that is the way to go. Has someone else ever ran into this error? I have seen some posts on the forum with similar error, but they are not providing any solid answers. In my case, I need to know how to implement either a config file for conn strings, or how to deploy with conn strings which retains password. I can connect to a remote SQL Server from SERVER A utilizing SQL Management Studio, therefore any firewall/port issues are not relevant. I feel the issue is with conn strings, and I need some solid advise on how to implement them especially, where in my case, one of my connections is to a remote sql box, utilizing Sql server authenticaion(which involves use of password). Thanks.
Here is how my current connection string looks like and notice there is no attribute for password:
Data Source=192.1.1.1xxx;User ID=username;Initial Catalog=databasename;Provider=SQLNCLI.1;Persist Security Info=True;
I have a Save method in my class file that connects to my sql db to 4 datasets, which I then wrap together. I am currently instantiating 4 different Sql Client connections to do this. Would it be more efficient to use just one?
We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.
I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:
- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?
-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/
Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?
Is there a way that would allow me to deploy ssis for end user in a given file, where the end user simply clicks some icon that will tiger the ssis deployment process?
Hi All, I am able to execute my ssis package from BID environment using SQL Server Authentication. However, when I deploy the package on SQL Serve 2005 using Windows Authentication, the package fails. The package is a simple one, it read two servers, truncate tables and load the data on the destination server.
I am not sure why this fails when I execute the package on the server environment, but works fine when I run it from BID.
I am looking at deploying some ssis package's as files, my question can I install just integreation services on on server to run the packages or do I need the full installation of sql server 2005
I would like to know how to set up the SSIS package with the DATABASE, SERVER NAME and Connection String into variables, so that I can change these parameters of the deployement, on another name of server and database (and connection string).
Hello, What should I do to change the server name and the database when I deploy a package on another machine ? SHould I change the value inside the configuration file (format XML) ? Thanks in advance for your advices !
I ahve read previous posts on this but didnt get a clear answer for what I am trying to do.
I have couple of SSIS packages running in y dev environment, we will be moving this into production soon. These pachakes will be called from the webapp to run once the flat files are uploaded.
Production environment has separate web server and Sql server box. Now how do I deploy the packages and where...on webserver or sql server box?
workflow is user uploads a file which is saved on the webserver and then the package will start to upload the data to the SQL Server db. I have seen that while storing the location of the flat file in the configuration file for the package it stores the hard coded path of the flat file is there a way for to save the UNC path or how can I tell the SSIS config file to look for the file on the webserver?
During deployment if I want to save the SSIS packages on to the SQL server do I still have to install SSIS components on the webserver...? or if I choose to save it as file system on the Webserver what do I neeed to install on the webserver so that the packages can be executed from the web app?
Excuse my long post but wanted to give a clear overview of the problem...
Several SSIS are deployed from a server A to a server B (Integration Services) with a SQL user and are stored in MSDB - Most of the SSIS have their connection only after SSIS have been launched ; Delayvalidation is set to true. - Application user the same SQL user from server A can launch the SSIS. - But the same application on server C cannot launch the SSIS. The error is : OLE DB Connection error for the connection string - Server A, B , C are Windows 2003 Server and belong to the same Windows Domain. Can you suggest me some tips ?
I would like to deploy my SSIS project on the production server. As far as I know from the last deployment, I had to change configuration managers to data sources to the ones of the production server. I had to go through all packages and change it. What is the best way to do this? I.e. Can I do it through a configuration file?
In this posting they talk about setting up a configuration file to setup the connection string, but I am not sure how to apply that connection string to my connection manager. When I go into my configuration file, I can see the connection string, and when I go into the advanced properties of the connection manager, under the "Named ConnectionStringFileName" property, I set the property value to the name of my configuration file, but when I test the connection I get an error that the connection string property has not been initialized.
Can someone please assist me with identifying what I am doing incorrectly?
Possible or not? --> I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations
In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?
Function SetDTSenvironmentVariables( environment ) Folder = "MyDtsPackageFolder" Select Case environment case "DEV" DTSGlobalVariables("WorkingDirectory").value = "C:Packages" & Folder case "STAGING" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_productionSTAGING" & Folder case "LIVE" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_production" & Folder End Select
' ' Set Connection Properties ' dim oPackage, oConn set oPackage = DTSGlobalVariables.parent oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "LogsErrors.txt" For Each oConn In oPackage.connections Select Case oConn.Name case "My_DB" Select Case environment case "DEV" oConn.datasource = "SERVER01" oConn.Catalog = "My_Production" case "STAGING" oConn.datasource = "SERVER06" oConn.Catalog = "My_Staging" case "LIVE" oConn.datasource = "SERVER06" oConn.Catalog = "My_Production" End Select case "Schools.xls" oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & "School_Codes.xls" case else oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & oConn.Name End Select Next
set oPackage = nothing set oConn = nothing End Function
I have developed the SSIS project, but I have a problem with deployment. My users are statistic people and SSIS packges they use are cleaning the raw data for them. Since they would like to run this SSIS package by themselves whenever they want, I need some deployment method that is easy for them to run (they are really not IT oriented people). The problem I encountered is that there is no visual studio (integration service client) installed on there computer, but they are allowed to access several dbs and server on the net.
Can someone tell me what is the best solution for this type of deployment?
I'm very new to the use of SSIS in a real enterprise environment. I've used DTS in the simplistic method of import/export but little else. As myself and a few others new to the tool are working through some things two questions have come up.
1.) What is the best way to execute an SSIS package using Java?
2.) Is it possible to deploy an SSIS package to a server that does not have SQL Server installed? I've read a few things that suggest that embedding the SSIS runtime environment in an application is possible but definitely not a beginner type operation.
Any quick guidance you can provide would be greatly appreciated.
Sorry to start a new thread, actually Darren answered my first question on SSIS dependencies but I have a clarification and somehow the software of this forum doesn't allow me to post again on my previous thread. (my posts disappears after some time).
My question is that if I choose to deploy the SSIS packages in SQL Server, then SSIS would have a dependency on SQL Database.
Do we know where the deployed packages are stored in SQL. This information is needed so that I can maintain and monitor the DB where the deployed packages are stored.
Also, do you think its a best practice to deploy the packages in SQL ( I think it will be good because then I can have consistent version of a package deployed on many machines... since I store my packages on one consistent DB rather than multiple file folders on each machine).
1) Every package has to have its own configuration file? There's no configuration on a project level? 2) is there anything in particular I have to do with a configuration file before running a build? When I run deployed package it seems like it ignores my configuration file and takes the connection string of conn. manager i use in development.
Hello, we are interested in upgrading our Data Warehouse environment to SQL Server 2005 to take advantage of SSIS. Our current data warehouse environment consists of 2 production servers with over 900 DTS packages between the 2 servers.
Are there any white papers out there detailing what type of deployment works well for an Enterprise wide use of SSIS? We're trying to determine if we should use SSIS on the same server hosting our databases, or separate SSIS onto its own dedicated box. If we went the dedicated route, what type of horsepower would we need on the SSIS box (procs, RAM, etc.)? Also, if we went the dedicated route, does it make sense to have 1 SSIS server that handles the data warehouse packages and the OLTP packages, or should that be separated into 2 SSIS servers having one server for each environment?
Thanks for any help you can provide on this subject!
I am trying to write a simple application which simplifies package deployment to SSIS. Basically it allows one to select a folder which contains dtsx-packages and the it would upload those files to the MSDB in a folder of choice on the MSDB. To do this I thought it would be nice to use the Integration Services API (Microsoft.SQLServer.Dts) and use the objects DTS.Runtime.Application and DTS.Runtime.Package.
The result would be this in a nutshell: Dim dtsApp As New Dts.Runtime.Application Dim pkg As Dts.Runtime.Package pkg = dtsApp.LoadPackage(File.FullName, Nothing) --loading the package from the filesystem dtsApp.SaveToSqlServerAs(pkg, Nothing, strPackagePath + PackageName, strDestinationServer, Nothing, Nothing)
However if I try to execute this the I end up with an Strace-assertion error. Searching for that error lead me to the following post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2173800&SiteID=1
After some more research I found the cause of my problems. I only had SSMS (The workstation components) installed on my development-pc. Someone suggested that installing Integration Services would solve it and after testing this it indeed solved it, but this still didn't solve my problem. The tool I am writing is going to be used on production-workstations which only have the Workstation components installed and installing Integration Services there isn't likely going to happen. Mostly because having SSIS installed locally doesn't have a function; the packages will be deployed to a remote server right after they are loaded from the file.
Trying to find an alternative lead me to Dtutil which miraculously enough does seem to work if given the right parameters, but to me this feels like a dirty solution. I feel it shouldn't be necessary to resort to using System.Diagnostics.Process to manually start Dtutil in the background when I have this nice api specifically designed for SSIS.
Is there anyone here that has a suggestion or an idea how to deploy packages using the Dts.Runtime.Application without installing SSIS on my local workstation?
I'm all mixed up on this one. Please comment on either of the following two remarks.
1. When I create a new SSIS package under BIDS I gather it is intended for development purposes. .I can test the package and run it but I need to"deploy" it (possibly to another server) to make it available for others to use.
2. Is "deploying" essentially copying my package (.ldtsx) to a new server or does it involve a lot more?
3. I read that I can create an SSIS package without actually needing to have sql 2005 installed on my box. Suppose I create a package that makes no reference to any databases. To *run* this package will I need sql2005 installed or can BIDS do this?
I've written several custom Control Flow and Data Flow components for SSIS. I'm trying to deploy a new Control Flow Task, however it will not show up in the "Choose Toolbox Items" window.
I have my dll's all in the right place. Because of other painful issues we have not used the GAC, and instead have put our DLL's that are referenced in the following directory: C:program filesMicrosoft SQL Server90DTSBinn. This has worked fine for other custom tasks, but not for this one.
The only things that are different about this transform are the following:
1. Not only does the task inherit from Task, it also implements two interfaces that I wrote.
2. One of the assembly references is actualy an executable instead of a dll. This is unusual, but I've done it with other .NET projects and not had any trouble.
I could move the code I need from the EXE out to a DLL. If somebody knows what could be causing this please respond?
hello, i got this error when i tried to build my project prior to deployment. S.O.S. Error 1 System.ApplicationException: Could not copy file "D:Documents and SettingsMy DocumentsVisual Studio 2005ProjectsSSISSolutionSSISSolutionPackageConfig.dtsConfig" to the deployment utility output directory "D:Documents and SettingsMy DocumentsVisual Studio 2005ProjectsSSISSolutionSSISSolutioninDeployment". ---> System.IO.IOException: The file 'D:Documents and SettingsMy DocumentsVisual Studio 2005ProjectsSSISSolutionSSISSolutioninDeploymentPackageConfig.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0
I try to deploy a SSIS Package with the method SaveToDtsServer of Microsoft.SqlServer.Dts.Runtime.Application.
My C# project is to deploy a SSIS Package in a Setup Application (My project has a Installer Class). It's work several times, but suddenly It stop working without change in my code.
There's a System.BadImageFormatException : Message="Invalid access to memory location. (Exception from HRESULT: 0x800703E6)"
Source="Microsoft.SqlServer.ManagedDTS"
StackTrace:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at TestPackageDeployment.Form1.button1_Click(Object sender, EventArgs e) in D:DEVTestPackageDeploymentTestPackageDeploymentForm1.cs:line 41
I try the SaveToSqlServer and SaveToSqlServerAs, but it doesn't Work, it's return the same exception. On the other hand, the method CreateFolderOnDtsServer works perfectly.
It' happen to me on a Win2003 and WinXp. I Use SQL Server 2005 + VS 2005.
I am using Package Configuration to simplify SSIS package deployment process. All the configuration information are stored in XML file. So far so good, However, since I have many, 20, packages. For each package, there is one configuration file to it. During the deployment process, I have dynamically modify connecting string (server name, DB name) to new ones. It ends up 20 or more modification and it's eaily for me to make mistake. Is there any workaround such as setting up environment variable, I guess, to allow me only modify once and apply it to all the packages?
I created a setup package and installed it on my SQL2005 server. I cant seem to find anywhere on the server where they are installed, but I can access them from the Job schedule tool, which work for me.
However, now I have to upgrade the deployed SSIS and I cant seem to install on top of an existing one (gave me no errors but after testing it i realized it is still running the old version.)
My questions are:
1) How can I find and delete these SSIS packages from the server? 2) How can I just upgrade for a new version of a SSIS package?
I need to be able to do both things as at some point I may want to just remove some packages.
I have been battling the whole day to get my project working on our sql cluster.
My project contains file system packages parent(control) packages etc. I cannot get any package to execture after deploying and configuring the packages with the manifest.
When I try to execute even a simple package, i get the following error:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 11/09/2007 15:06:59,SGA Staging ETL import,Error,0,BRY-CLUSSQL-1,SGA Staging ETL import,(Job outcome),,The job failed. The Job was invoked by User sgainterface. The last step to run was step 1 (Test).,00:00:00,0,0,,,,0 11/09/2007 15:06:59,SGA Staging ETL import,Error,1,BRY-CLUSSQL-1,SGA Staging ETL import,Test,,Executed as user: SUPERGRPSvcSQL. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:06:59 PM Error: 2007-11-09 15:06:59.45 Code: 0xC0011007 Source: {8B6231A9-8E5B-47BC-B54F-6A448B93B085} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2007-11-09 15:06:59.45 Code: 0xC0011002 Source: {8B6231A9-8E5B-47BC-B54F-6A448B93B085} Description: Failed to open package file "\10.202.29.35SGA Import ConfigsSGA Staging Deployment PackagesActive PackagesSGA ETL Step 1.1 - Prepare Branch Master File.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML f... The package could not be f... The step failed.,00:00:00,0,0,,,,0
Has anyone ever gotten this error? Is it permission related? I have already granted the sql user for this project ownership over the database but I think that the agent does not have permission to access those file system packages. This might be a problem since it is a file system package...if so, how do I grant the agent access to an NT file system?
I have designed a few SSIS packages in the BI Development studio and deployed them to the msdb database of my SQL server using the generated Deployment Utility.
They deployed and executed just fine, but, I would like to better organize them into folders within the msdb storage area.
Is there a way to tell the project or the generated Deployment Utility to deploy the packages to a specific folder within the SSIS Packages / msdb storage area on the SQL server?
Based on some of the threads, when I enabled package configuration, I changed the path from C:Karunakaranfolder1folder2Configcommon.dtsconfig -> common.dtsconfig
Once I did this, when I open the project I get a dozen of warning similar to the one below
Warning loading <package1>.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.
What am I doing wrong here? If I have to deploy this to another box, what should I do to ensure that nothing breaks once its copied / installed?
I have used the SSIS import/export wizard to create a transfer from one database to another.
Q1: Why are some files from this wizard stored in the the temp folder ?
Example: C:Documents and SettingspbhLocal SettingsTemp
This is extreemly unuseful when checking in the solution in tfs since these files will not be saved.
Q2: I have tried to move these files and manually edit the location for these file references in the dtsx package file. However: The connection string wants an absolute path for the files..
Example:
DTS:Name="ConnectionString">c: mp1EA.tmp
How can I refer to $project$ mp1ea.tmp, a relative path to the project directory instead?
