SSIS Database Connection Problems And Deployment Problems
Dec 31, 2007
Well, I'm been trying to resolve my problems through searching previous post, but I'm starting to go around in circles. This is what my problem is: Originally, I created a SSIS package using windows authentication. Once I completed the work, I wanted to change the connection to SQL Server authentication and use xml configuration for deployment.
First problem: I edit the xml file, but can't use the connection
I double clicked the Connection Manager connection and changed the log on from Windows to SQL Server. Password would not save, so, I read about what most posts have been about and I can go into the SIS menu, choose Package Configurations and create a xml file with the name of my connection manager.
Then, I exit the package and go into the xml file and enter the password=XXXX; stuff right after the user Id. When I load the package, the connection does not load?? Shouldn't the package pick-up the password now that I have entered it? I used note pad to edit the xml. If I go into note pad, I can see the edit. If I use SSIS package config to view the file, I don't see the password. I'm also having sql agent task problems, but I think I will be able to solve this if I can get the package to run with the password in the design mode. Can anyone help me with this?
View 15 Replies
ADVERTISEMENT
Aug 15, 2007
Hi,
Please can you let me know which of the following 2 is a better method to deploy SSIS packages
File System deplyment OR SQL Server deployment
What are the advantages of one over the other?
Thanks,
Mrinali
View 4 Replies
View Related
Aug 31, 2007
Hi,
I'm using the reports designer in Visual Studio 2005 Professional and have successfully been deploying reports to my client's server for several months. Now I can't deploy any reports, when I try Visual Studio almost hangs, tries for about 5 minutes to deploy a single report and then fails with an error message:
TITLE: Microsoft Report Designer
------------------------------
A connection could not be made to the report server http://winserver/ReportServer.
------------------------------
ADDITIONAL INFORMATION:
The operation has timed out (System.Web.Services)
------------------------------
BUTTONS:
OK
------------------------------
It used to ask me for username/password for the server (credentials), now it doesn't do that. I can go to the reports server and reports interface pages in my browser OK. The reports build without any errors.
Has anyone experienced this? Any ideas on how to get it working again?
Regards,
Greg
View 2 Replies
View Related
Apr 27, 2007
Hi,
I used OLE DB for oracle connection provider, it takes only Server name, User id, Password but along with it,
I want to give it Database name like in OLE DB for Sql Server.
Is it possible to provide it in some other way, so that i can put DataBase name directly also.
Thanks
View 12 Replies
View Related
Nov 22, 2007
Hi
i can install and run my package in my development system work fine.
The same package when i was installing in the production system error
error: the acquire connection method call to the connection manager "con name" failed with error code
0xc0202009
I used sql server authentication and
tried the protection level
both encrypted password protected and sensitive with user key
Ezhil
View 4 Replies
View Related
Sep 29, 2006
Hi,
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?
Thanks in advance
View 5 Replies
View Related
Dec 18, 2007
When I try this code in an SSIS "Script Task":
Dim dbConnectionManager As ConnectionManager = Dts.Connections(0)
Dim dbConnectionRaw As Object = dbConnectionManager.AcquireConnection(Nothing)
Dim dbConnection As OdbcConnection = CType(dbConnectionRaw, OdbcConnection)
I get this error:
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.Odbc.OdbcConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
I'm just trying to get a basic database connection from the DTS package in my
script task. Is there a better way to do this? Preferably, I would use ADO.NET
rather than the old COM stuff. However, if SSIS still requires the use of COM,
that's fine as well, as long as I can fix the above code.
Thanks in advance!
View 14 Replies
View Related
Sep 13, 2007
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?
View 2 Replies
View Related
Oct 19, 2007
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.
Any help would be greatly appreciated.
Thanks
View 4 Replies
View Related
Mar 11, 2008
Hi
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
Bimal
View 4 Replies
View Related
Jun 1, 2006
Hi,
I would like to deploy my SSIS Package in my web server rather than Database Server. So anyone please give me some suggestion to implement this.
Also I wanted to know whether I should have a SQL Server instance in the server where I am deploying my SSIS package?
Its pretty urgent. Help please.
Thanks & Regards,
Prakash Srinivasan.
View 3 Replies
View Related
Oct 18, 2006
Hello,
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).
Thanks a lot for your advices !
Marie-Thérèse
View 7 Replies
View Related
Oct 18, 2006
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 !
View 4 Replies
View Related
Oct 23, 2006
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...
Appreciate your help in advance...
Regards
View 4 Replies
View Related
Jul 14, 2006
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 ?
View 1 Replies
View Related
Apr 16, 2008
Hi all,
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?
Thanks.
View 1 Replies
View Related
May 15, 2008
Hi,
I have an reporting services (SSRS 2005) installed locally and I am trying to access it locally through : http://localhost/reportserver, but all I get back is this page.
localhost/ReportServer - /
Microsoft SQL Server Reporting Services Version 9.00.3054.00
Also when I try to connect to reporting services through SSMS I get the following error message.
Cannot Connect to Server ServerNameSQL2005
Unable to connect to the remove server (Microsoft.SQLServer.Management.UI.RSClient)
No connection could be made because the target machine actively refused it(System).
About my set up, I have SQL Server 2005 (SP2) installed in a named instance called SQL2005, I have reporting services set up to run under local system and I have gone through the RS config tool and everything in there appears to be correct. U double checked in IIS that the report server folder is "ReportServer" and not "ReportServer$SQL2005". I have set up full read write permissions to all the virtual directories, to my domain account which is also a local administrator. The user logs in under windows authentication (marked in the directory security tab in IIS, for the virtual directory & the default website).
Whenever I start the service however I get the following errors in the windows application log:
Report Server (SQL2005) cannot create the trace log C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__05_15_2008_10_27_52.log.
&
Report Manager cannot create the trace log C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerWebApp__05_15_2008_10_27_07.log.
Also I am unable to deploy my SSRS 2005 project from BI studio with more errors, I get the following exception thrown in a windows. (I am trying to deploy to the root "ReportServer" folder, not a sub folder.
A connection could not be made to the report server http://localhost/reportserver.
System.Web.Services.Protocols.SoapException: Server was unable to process request ---> System.InvalidOperationException: Unable to generate a temporary class (result=1)
error: CS2001 "Source File "C:WindowsTempc-zsc7th.0.cs" could not be found.....
I don't have sharepoint installed, I do have the dashboard designer for Performance Point Server 2007 installed though.
I have been on this thing for hours, I have tried appending "$SQL2005" on to the end, stopping and starting RS Service multiple times. Change all the permssions in IIS so that the user can full control over the IIS virutal directory. I am at a loss at what else I could try.
Any Idea's?
Thanks!!
View 1 Replies
View Related
Dec 15, 2006
Hallo,
I'm currently strugling with the setup of our packages for deployment to a new environment.
We are working with a master/detail package setup. One master package is created that will call all child packages. In the master package we don't have any connection towards our source and/or target databases/sourcesystems.
Everything works fine, however, starting to deploy the whole set of packages, it seems that we don't have the option to set specific properties of our detailed packages, e.g. connection properties. But this is just what we need.
When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment.
I tried to fix this with parent package variables, but I can't set the password property, only the ServerName and UserName can be set, not the Password.
Anyone an idea what the easiest and best approach is to solve this burden?
Thx
View 1 Replies
View Related
May 1, 2008
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
Can anyone help?
View 6 Replies
View Related
Oct 12, 2007
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?
View 4 Replies
View Related
Apr 12, 2006
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.
View 3 Replies
View Related
Mar 8, 2007
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).
regards,
Abhishek.
View 1 Replies
View Related
Oct 31, 2007
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.
View 10 Replies
View Related
Jun 13, 2006
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!
Eric
View 1 Replies
View Related
Feb 25, 2008
Hi all,
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?
View 6 Replies
View Related
Apr 5, 2006
I'm working through the SQL2005 Reporting Services Tutorial. When I try to deploy, I get the error "Connection could not be made to the report server." It appears that I do not have permissions to get to localhost/reportserver (I get a permissions error when just trying to get there directly from "run"). In another forum I saw several people having the same issue. One contributor said that the problem was solved for him by uninstalling SQL2005 and reinstalling using the local system account rather than a windows domain account. I tried that, but am getting the same error. Anyone know how to fix this?
View 18 Replies
View Related
Aug 6, 2015
I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
Is this possible, if so how?
View 0 Replies
View Related
Oct 10, 2007
Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed.
Thanks for your help!
Sue
View 14 Replies
View Related
May 26, 2006
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?
TIA,
barkingdog
View 3 Replies
View Related
Oct 4, 2006
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?
View 4 Replies
View Related
Oct 24, 2007
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
View 3 Replies
View Related
Jan 27, 2006
Hi,
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.
here is my code :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo;
using MSDR = Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Management.Smo.Agent;
namespace SSIS_API
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkgSSIS = new Package();
pkgSSIS.Name = "Integration";
try
{
//app.SaveToSqlServer(pkgSSIS, null, "BGSXP-267", null, null);
app.SaveToDtsServer(pkgSSIS, null, @"File SystemmyPackage", "BGSXP-267");
}
catch (Exception err)
{
MessageBox.Show("Erreur : " + err.StackTrace.ToString());
}
finally
{
this.Close();
}
}
}
}
View 7 Replies
View Related