Oracle Connection Information In SSIS Package.
May 2, 2007
Hi,
I want to make a SSIS package with Oracle and deploy it in no of oracle databases, for it every time I have to open package and change connection information.
How can I make oracle connection information as variable value so that when I deploy my package on Oracle database it will pick all oracle connection information(User Id, Pwd, Server Name) automatically.
Please let me know about this.
Thanks
View 4 Replies
ADVERTISEMENT
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
Aug 14, 2006
Hi There,
In SSIS package development environment, I was able to connect to an oracle database and pull data into my sql server database. I installed the client tools for oracle and I put an entry into the tnsnames.ora and I was able to connect.
But in production environment, if I deploy the package on sql server, I was wondering if I had to do the same job of downloading the oracle client tools onto my production machine --which creates a tnsnames.ora file to it default location and then edit it with tthe tns entry-- or is there a better way to do this--avoiding the download?
Can somebody plzz help me ?
Thanks.
View 10 Replies
View Related
Jul 12, 2006
Hi all,
I am supposed to maintain a bunch of excels documenting all the mappings I'm doing in SSIS. The excels have the following format:
Target Field | Target Type | Source Table | Source Field | Source Type | Transformation Rule
Apart from being incredibly tedious, it is hard to keep every excel current, as there are other people adding and taking information from the database model.
I plan to extract this information from SSIS and create the excels dinamically, but, apart from parsing the .dtsx for each package, I see no other way of getting what I need.
Any suggestions on how I should do this? Is there an easier way?
Cheers,
David Leal
View 4 Replies
View Related
Nov 22, 2007
hi all,
i have a package; thats executing another 4 packages, i can able to execute these packages in ASP.net
I want to display the status notification of the executing packages in the webpage( while packages are being executed, i want to display which package is executing, and what is the status of the package! whether it is executing or not? or it is raising any errors?) every thing i want to give in the front end--> its not my requirement, my client ask me to do that!
so please help me! how to display these status information of the running package in asp.net (c#)
(i'll give you one good chocolate if you give me good solution!)
post your mails to vivekanandaraj.natarajan@honeywell.com Thanks you a lot!
View 1 Replies
View Related
Nov 14, 2007
What is the best way to run SSIS scripts on different servers without changing connection information. Our test server is ppntt140 and our production server is ppntd110. If I create a script on server ppntt140 what can I do so I can move it to server ppntd110 without changing any connection information? Database names are the same, it is just the server that changes. What is the best way to handle this? Thanks in advance.
View 4 Replies
View Related
Aug 16, 2006
Hello
When running a package in VS you can see something like this in the output window:
SSIS package "logging.dtsx" starting.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "C: est ssis loggingad_data1.txt" is 477.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [1011]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C: est ssis loggingad_data1.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (87)" wrote 0 rows.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1011)" wrote 1 rows.
SSIS package "logging.dtsx" finished: Success.
This is exactly when I need when a package is running but I want to be able to see it without using Visual Studio.
I would do it in Reporting Services but I need to find out to get the information. The SSIS logging feature in a package does not provide that kind of info.
Did someone try this already?
Thanks!
Mop
View 6 Replies
View Related
Jul 6, 2006
Dear All,
I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?
Regards,
Strike
View 8 Replies
View Related
Jun 20, 2006
Hi
I have a number of scripts that run against an oracle server to retreive data to manipulate before populating a sqlserver database. I am connecting using an Odbc connection and using the DataReader Source to read from the Oracle table. It all seems to work ok.
When I have tried to extract data from an Oracle view in the same way it doesn't seem to work. The error message mentioned the PrimeOutput() method and error code 0xC02090F5. The view is very big so is it possible that the connection simply timed out?
Any ideas?
Thanks
B
View 6 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
Feb 6, 2008
Hello there,
I'am asking you people if it's possible to invoke procedure, functions in package in Oracle database > 9.X with SSIS package...
Thx for your incoming answers
Regards,
KiK2k1
View 1 Replies
View Related
Sep 7, 2006
I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:
Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener
ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.
I have tried OLEDB connection and that does not work either.
Any inputs will be appreciated.
Thanks,
Monisha
View 6 Replies
View Related
Jan 14, 2008
hi,
Below are 2 oracle commands
1. (calls an oracle package) - call lpaarchive.pibrdg.setlastbridgeruntime(sysdate);
2. (selects rows) - select timestamp ,
pitag ,
rtlmp from LPAARCHIVE.I_PIBRDG_BUS5MINRTLMPS
where timestamp <= (sysdate + 0.002777778)
order by TIMESTAMP ASC;
I need to execute these 2 statements together. I tried using Execute SQL task to call the package and then an OLEDB source that calls a variable with the select statement. But it does not retrieve any rows. It seems like the result of calling the package is used by the select statement to give the final rows. Could anyone please help me resolve this issue.
View 5 Replies
View Related
Mar 29, 2007
Both the OLE DB Connection and ADO.Net Connection in SSIS Package does not remember password.
Im connecting to a SQL Server 2000 box using its sa password as test.
The SSIS package runs fine when you first set up the connection in bids
The bottom line is that SSIS keeps forgetting the password I feed into
the two Connections that I'm using. I double-click a connection,
type the password in, check "Save my password" and hit "OK" but the
password disappears from there whenever I run the package or
double-click the connection again.
is there any known workaround for this issue as I would like to schedule my SSIS package using a SSIS Step in a SQL Server 2005 Agent job.
the only thing I found when googling this error was link below but the workaround described here is a little harsh
http://www.developersdex.com/sql/message.asp?p=1921&ID=%3C1146409399.447345.7470@j73g2000cwa.googlegroups.com%3E
thanks in advance
Dave
the box SSIS is running on is Windows 2003 Server Standard Edition latest service pack
SQL Server 2005 (no service packs )
View 14 Replies
View Related
May 16, 2007
Hi All!
I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...
I wrote this code:
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
{
ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;
if (cmAdo == null)
throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");
this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;
}
but the 'conn' is ALWAYS null...
I tried
this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;
too, but no result: the 'conn' is null again...
If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand
could you help me plz?
View 6 Replies
View Related
Mar 5, 2007
Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
View 7 Replies
View Related
May 18, 2007
As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.
At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.
Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.
For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.
Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.
So, I am am wondering, what is the best way to address this?
My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.
This section of the dtsx file shows the values of interest:
Code Snippet
<component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4">
<properties>
<property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property>
<!-- More Properies -->
</component>
Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.
But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.
I would sincerely appreciate any suggestions on how to accomplish this.
Rick
View 6 Replies
View Related
Dec 8, 2011
I need to delete some records in a Oracle RDBMS based at a SQL Server's query. I'm using the follow structure SSIS's package:
View 4 Replies
View Related
Jun 20, 2006
Hi ,
i was used the Follwing DataFlow for my Package.using Oracle 8i
FalteFile Source -------------> Data Conversion --------------->OLEDB Destination (Oracle Data table)
using above control flow to map the Source file to Destination . When i run the SSIS Package teh Folwing Error i got
"Truncation Occur maydue to inserting data from data flow column "columnName " with a length of 50 "
regarding this Error i i understood its for happening Data Length . so that i was changed the Source Column Length Exactly Match with the The Destination table.
still i am getting this Error. pls any one give me a solution . SHould i Change the DataType also?
pls give your suggestion
Thanks & Regards
Jeyakumar.M
chennai
View 3 Replies
View Related
Sep 4, 2006
Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.
1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
2)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
3)
[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.
View 1 Replies
View Related
May 14, 2007
Hi,
In BI Tool SSIS Packages run fine and get data From Oracle and Save it in SQL Server.
Package Protection Level is EncryptSensitivewithPassword.
In BI tool when i open the package it ask password and then run fine.
If i change the Protection Level to Dont save Sensitive,
It does not run fine in even BI tool.
It is fine if i use EncryptSensitivewithPassword.in BI Tool and run it.
Now the problem is that i need to run this package through SQL Job.
so Job give error
"Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information."
Please i need help ?
Thanks
View 7 Replies
View Related
Jun 4, 2008
[Connection manager "CLRDB_Connection"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'CLR.Test'.".
I got this error i cant resolve this. i deployed the package to other server using sql authentication. and i create the package using windows authentication on my local computer. i try to run my package in my local computer and i got this error.
View 2 Replies
View Related
Mar 1, 2006
I am having a very frustrating issue--
Essentially I have a package with an OLE DB Connection manager and a Data Flow Task.
The OLE DB Connection points to my SQL Server (using SQL authentication with a user who has sysadmin and dbo access to the DB)
All the Data Flow Task has is an SQL OLE DB Transform that runs a
SELECT * FROM <Table>
Obviously, this package does nothing...but I have stripped it down for test purposes.
Long story....longer:
When I debug this package from my local computer, the package completes successfully.
However, when I build the package and import it as a job on the server, it fails when it runs.
I get the following error in the Event Log:
Login failed for user 'xxxxxx'. [CLIENT: <local machine>]
I enabled logging on my package; it says:
OnError,....The AcquireConnection method call to the connection manager "xxxxxx" failed with error code 0xC0202009
Why can I debug the package successfully on my computer, yet when the server runs the package, locally, it fails?
View 6 Replies
View Related
Apr 27, 2008
Hi,
I am new to SSIS and i have to develop a ssis package which will run in a production machine through VB.Net(2003) exe.I am facing a problem while setting connection string of SSIS package dynamically.Can anybody help me on this?
View 1 Replies
View Related
Mar 13, 2008
I have created a SSIS package to import data from DBF files using a OLE DB Jet 4 driver, which works very well in Visual Studio, however doesn't work via a SQL Job. It states the the path isn't valid.
The file path in the connection manage is \servernamedbf (dbf being a shared folder I have permissions and security to)
The server is on another domain, which is trusted. I added the server's IP address to the host file and through windows explorer can navigate to the dbf folder with out a password prompt.
Why can't I get this to work???
Cheers
View 1 Replies
View Related
Aug 4, 2006
It seems there a lot of problems running SSIS packages under the sql agent. I have read the knowledgebase articles regarding permission issues etc but I still can't get my job to run. I can run any package as a job apart from a package that connects to an external database via an odbc connection. Has anyone had any luck with this and can let me in on the secret.
View 14 Replies
View Related
Feb 5, 2007
Hi!
I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.
Finally I need to load data to a database on a different machine using this package.
I used several scenaries:
1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.
2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).
3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.
In all cases I recieved the same error execution message :
"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."
As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.
As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.
Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.
View 9 Replies
View Related
Jul 19, 2007
Hi,
We are using a €œFlat File Connection Manager€? in our SSIS package.
The package fails occasionally while loading in the validation phase with the error
€œ-1073659899,0x,The connection type "FLATFILE" specified for connection manager "<some name>" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
This error is not returned always.
Also I feel €œFLATFILE€? is a valid type of connection manager. This value €œFLATFILE€? is inserted by the editor and not manually typed. This is a weird behavior of SSIS.
Sometimes I get this error with respect to some other connection manager used in the package as well. €œOLEDB€? is the type of that other connection manager.
Has anybody faced similar issue earlier?
Please let me know some thoughts, suggestions, and possible work-arounds to avoid this error as this is very critical for us.
Regards
Madhavan.TR
View 2 Replies
View Related
Jul 11, 2007
Hello All,
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
View 5 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
Jun 19, 2006
Hi
I need help for Connection string:
Requirement: When we create SSIS Pacakge using Businessinteligence studio.Each Source and Destination or whatever we using the Control required DB Connection.
we connect theDB server and Database Table through manaully .Instead of Manual i need dynamic Global varible for Connection String .How to achieve this connection string.
because suppose we create SSIS Package in Developement Server Latter We change the Server from Developement to Another Testing Server . at that time we dont requierd for changing manulay.any one pls reply me.
Same as in Dotnet we give configiration XML file .we gave the Connection strng. how to in SSIS we do?
Thanks & Regards
M.Jeyakumar
View 9 Replies
View Related
May 29, 2008
Hello,
I've SQL Server running on 64 bit. When I schedule a package to run every night which has excel data transformation, it fails. Initally I had a problem running through BIDS also but then I found in project properties you can switch this off by specifying property Run64BitRuntime by false. After going through some forums, many have suggested that when scheduling a package, modify the command arguments to run as 32bit runtime. These command argumnents can be generated from making use of tool DTExecUI.exe Even after using generated command arguments from this tool, it doesn't help.
Below is my command argument and the error message generated.
/FILE "D:DevDWLoadAll.dtsx"
/CONFIGFILE "D:DevDWdevCongig.dtsConfig"
/CONNECTION "customers.dtsx";"D:DevDWcustomers.dtsx"
/CONNECTION "Addresses.dtsx";"D:DevDWAddresses.dtsx"
/CONNECTION "Counties.dtsx";"D:DevDWCounties.dtsx"
/CONNECTION "Countries.dtsx";"D:DevDWCountries.dtsx"
/CONNECTION "ExcelLoad.dtsx";"D:DevDWExcelLoad.dtsx"
/CONNECTION Test;""Data Source=TestDW;Initial Catalog=TestStagingDW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
Message
Executed as user: DWUserSQLServiceAcc. ...0.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 00:05:00 Error: 2008-05-28 00:06:02.31 Code: 0xC0202009 Source: Excel Load Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-05-28 00:06:02.31 Code: 0xC020801C Source: Copy Data Excel Source [5052] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End... The package execution fa... The step failed.
Any suggestions???
Circuit
View 3 Replies
View Related
May 8, 2008
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database.
I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again.
This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails.
What do I have to do to make that password actually stick!?
Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio.
Here are at least some of the version information, which may or may not be useful.
Microsoft Visual Studio 2005
Version 8.0.50727.42
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
SQL Prompt 3.5
Thank you for any help you can provide.
-thursday's geek
View 12 Replies
View Related
