Hi,
I like to use a xml file by an url. This url is behind a password. So I have to enter a username and a password to see the xml file. I have the credentials so that is no problem.
Now I like to use this xml file to use as a source for my SSIS package. Is there a possibility to access this xml with SSIS?
Thanks!
Roel
I have a simple solution with (right now) 4 packages, 3 configuration files and one "global" datasource. Here is the View Code on the data source:<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" xsi:type="RelationalDataSource" dwd:design-time-name="02d8345e-665e-4bd8-9323-1e846af220e5" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ID>PPDM37 Staging</ID> <Name>PPDM37 Staging</Name> <CreatedTimestamp>0001-01-01T07:00:00Z</CreatedTimestamp> <LastSchemaUpdate>0001-01-01T07:00:00Z</LastSchemaUpdate> <ConnectionString>Provider=SQLNCLI.1;Data Source=engsql03.dev.ihs.com;Persist Security Info=True;Password=;User ID=;Initial Catalog=PPDM37_Staging</ConnectionString> <ConnectionStringSecurity>PasswordRemoved</ConnectionStringSecurity> <Timeout>PT0S</Timeout></DataSource>As you can see, the <ConnectionStringSecurity> node is set with PasswordRemoved. When editing the data source and checking the Keep Password option on it still saves with PasswordRemoved! I've even gone in and hand edited the above XML. The next time I view the XML it is set back to PasswordRemoved. How the heck do I keep the password with the connection string? I am pushing the connection string out to a global config file and it does not save the password there either. Extremely frustrating as it is causing us to hard code the password inside our packages which means it is no longer able to move from one server to the next without touching every single package.Suggestions? Is there a setting somewhere in SSIS that is doing this?
View 3 Replies View RelatedHello all, I have read many topics about this error but it doesn't fix my packages in my particular case. The problem is that I access to a database in Oracle using the Ole DB provider for Oracle
I get that the mentioned error when I try to run a job from the agent.
1 - When I set "DontSaveSensitive" to ProtectionLevel I get this error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: username/password no valid".
This is normal because the connection needs a password.
2 - When I set "EncryptSensitiveWithUserKey" to ProtectionLevel I get the mentioned error: "Failed to decrypt protected XML node "DTSassword" ..
The other options to ProtectionLevel doesn't work neither. I have tried to write the password in the field required...
I have tried many possibilities but nothing new, it doesn't work.
Does anybody have this error with Oracle as source file ???
Thank you .
After designing a SSIS package in Visual Studio 2005 that had two connection manager defined to keep the password. After I deployed the package to a file system. I then Imported the .dtsx file after making a Integration Services connection in Sql Server Management Studio. When I tried to run the package it failed when it tried to make the connection. When I edited the connection manager connection string and added the password and the package ran fine but it does not retain the password!. I need to have this package scheduled to run daily so I need to know how to have the package keep the password in the connection string. I have seen other posts on this issue but not seen a good solution. Could someone point me to the proper MSDN article that would explain how to implement this ? Is it a SQL Server configuration issue or a property in Visual Studio SSIS design time ?
thanks.
I'm using a shared data source to connect an Oracle server in my packages. After changing the database user password in the shared data source, I noticed the package concerned would fail with the following description.
Source: "OraOLEDB"Â Hresult: 0x80004005Â Description: "ORA-01017: invalid username/password; logon denied".
Is there a way to ensure the packages will use the latest information in the shared data source? I did do a Rebuild before executing the packages.
I have connected to Database using my credentials by checking remember password option. After few days I forgot my password. How can I recover the password as SQL remember it. Is there any way to recover my password instead of resetting it.
View 3 Replies View RelatedI have a package protected by a password - I am already unhappy that to get it to use the configuration file to change connection strings for the production servers I have had to hardcode the password into the config file - very insecure!
However, the package now deploys correctly to the production server and will run from there OK, but NOT if scheduled as a SQL Server Agent Job. Thus is because however often I edit the command line to include the password after the DECRYPT switch (which it has prompted me for when I click on the command line tab), the Job Step will not retain it.
If I open it up after I have edited it and closed it, the password has disappeared.
I know that if I run dtexec plus the code in the Command Line tab (with the password), the package runs OK.
This is driving me insane!
I have read all the other posts and so I tried replacing the SSIS package step with a CmdExec step and pasting that code into there - then I get an OLEDB error..
The code I use is:
DTEXEC /SQL "ImportRateMonitoringTables" /SERVER servername /DECRYPT password /CONFIGFILE "D:Microsoft SQL ServerSSISDeploymentsRateMonitoringImportTasksDeploymentImportRateMonitoringTables_Production.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and I get
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF
although the same code executes perfectly from a command prompt.
Please does anyone have any experience with a similar problem and if so, how did you get round it?
Thank you
I am receiving the following error message when attempting to create a new SQL Authenticated login id.
Password validation failed. The password does not meet the requirements of the password filter DLL. (Microsoft SQL Server, Error: 15119)
I have four servers all running SQL Server 2005 SP2 on Windows 2003 Ent. SP1. Of the four servers, only one received the above error message using the same TSQL below.
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'pvif9dal' MUST_CHANGE, CHECK_EXPIRATION = ON
All four servers are in the same domain, which if I understand correctly, the password policies are therefore inherited at the OS level by the domain. The password being used is within the password policies of the domain.
Any ideas as to a root cause?
I tried to install an ALLDATA database which run with SQL Server 2005 express edition. The data base fails to install becase of the following code that come up which is related to AS password requirement. The error that come up is:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=28001&EvtType=sqlca%5csqlcax.cpp%40SAPasswordPolicyCheck%40SAPasswordPolicyCheck%40x6d61
------------------------------
BUTTONS:
&Retry
Cancel
------------------------------
I am trying to install this database in a network server operating under Windows Server 2003 R2 with SP2. If anyone knows how to solve this problem, please let me.
Thanks,
Amilcar
On the login prompt for Report Server, there is a checkbox option to "Remember my Password." I check this, login successfully... but when returning to the Report Server, I am again prompted for the password, although the user name is saved. No matter how many times I do this, the password will not save in IE7. I have tried this on 3 different computers with IE7. In IE 6, I do not even get the checkbox as an option, just the user name/password prompt. In Firefox, the password saves fine. Any ideas what would be causing this?
Thanks,
Brian
I put this together to export the user name /password to a csv file to test my SP to output the user name/password.
DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
SELECT @user_name ,@psswrd
FROM ngweb_bulk_enrollments
EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C: est.csv -Sserver1 -T -t, -r
-c'
This works but I don't get the headers in the file. How can I include the headers?
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Â
View 4 Replies View RelatedHi,
I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task).
After that, in an xml task, I set the input properties like that:
Operation type: xslt
SourceType: File connection
Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90
Me.VariableDispenser.LockOneForWrite("RAS", v)
v("RAS").Value = doc.OuterXml
v.Unlock()
and in the xml task, I set configuration like that:
Operation type: xslt
SourceType: Variable
Source: user::RAS
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.".
Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
<?xml version="1.0" encoding="UTF-8"?>
<root> €¦
</root>
(When I store it in a file, I can see it in IE)
Have you got an idea ?
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
Hi
I am having a huge xml file with nested section.
i also have a xsd file for that xml.
i have a destination table where the data from the xml should be loaded into.
i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.
on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files.
also i would like to know any other better way of getting the data from xml source without using the joins.
Hari
hi ,
i am trying for a drill through report (rdlc)
ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like
An error has occurred during report processing.
A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".
the code is
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
using DAC;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
DataSet ds = new DataSet();
ds = obj.get_order();
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";
ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
Label1.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
string order_id = Label1.Text;
DataSet ds = new DataSet();
ds = obj.get_orderdetail(order_id);
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");
ReportViewer1.LocalReport.Refresh();
}
}
the code in method get_orderdetail(order_id) is
public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.
Hi,
I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?
Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************
How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));
db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;
dsNew.Update();
2)
RelationalDataSourceView rdsv;
rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));
rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);
*************************************************************
4)
// Make sure we have the name we thought
dss.Tables[0].TableName = tableName;
// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();
5)
MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";
Add columns:
ScalarMiningStructureColumn smsc;
// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);
*******************************************
6)
MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));
mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);
MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);
mm.Update();
Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.
I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.
Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?
Thanks in advance
ADG
hi everybody,
i want to create data source and data source view for data mining, with using C Sharp.
i have create data source and data source view and export to XML file, but when i change to another computer, run those XML file, it return error, when i run statement to create and biuld mining model, what can i change on xml or how to run XML on another computer sucessfully,
and have i build data source and data source view, how to do it.?
thanks for you helps
Today I was making a few reports.
When I tested the reports in Visual Studio, they worked great: I got the expected result.
But when I deployed the reports to our reportserver the problem started.
When I click on the directory in which my reports are deployed, I got my 4 reports.
Till now everything worked correct.
But when I click on a report to view the results it went wrong.
I got an error:
"Cannot create a connection to data source 'Live'"
(Live is the name of our data source).
We are using the Windows Logons and I am sure that I have all the rights on the server, I gave myself 'sysadmin' rights, so it should work.
I also have tried it with all the roles assigned on my account, but then it still won't work.
When I modify the data source, and set it to another server en database it works.
The datasource 'Live' exists on a x64 MsSQL server, en the other datasource is on a x86 MsSQL server.
Maybe that is the problem?
Can someone tell me what is wrong?
Hi there,
I'm trying to build a report for Windows Forms (C#) using .rdlc files.
But every time I run it, I get this error message. I've followed the step-by-step from MSDN instructions... but it doesn't work!
Could anyone help me?!
Tks a lot!
Luis Antonio - Brazil
I am building a website in Dreamweaver. I have tried and tried to get a connection to SQL 2005. Everything works on the web except the stuff that requires the use of my SQL tables. With OPEN_Conn ="Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=master" I get an error at the bottom of my page saying this:
Microsoft OLE DB Provider for SQL Server error '80004005'
Invalid authorization specification
When I add a password and ID to the string the pages become very slow and I get this error:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Using windows authentication when logging into SQL Server doesn't have a user id or password so maybe I just don't know what to put there. I have tried a mix of all the passwords and ID's I use on this computer. Do I even have to have the user ID and password and if not how do I fix the first error? All help appreciated.
Hi All.
Have a nice day.
in my project asp.net web.config file has
<connectionStrings> <add name="MyDbConn" connectionString="Data Source=NAVEEDSQLEXPRESS;Initial Catalog=QADB_2005;Persist Security
Info=True;User ID=db1;Password=12345;" providerName="System.Data.SqlClient" /> </connectionStrings>
I want to change password of database, how can i change. please can you assist.
Thanks.
Zahyea.
Hello ,
Is is correct that the 'sa ' passoword is stored in one of the files of the SQL Server and is easily readable if the location of the file is known ?
If not , then is the password encrypted when i try to connect to the SQL server on the Internet ?
Thanks.
Sameer.
I am designing a DTS package which i need to update every so often to change the name of the source file(it's a text import). Every time that i change this, then click save, the DTS job begins to fail, with password errors. I put the password back in correctly( i still don't know why it gets lost in the first place), and click save - same result. this is getting tremendously frustrating, so i really appreciate any answers.
View 3 Replies View RelatedI will like to Change the SA Password, Do i have to go thru all the DTS packages and Jobs to change it too?
Thanks
I need to get a password out of SQLServer 6.5. The account is a user account. I need to get the password so that I can create a development database. The DBA's that know the password have left the company, and I can not just change the password. Please HELP!!!
View 1 Replies View RelatedRecently my husband has taken the position of IS Manager for a major hotel Firm.
The IS Manager that was there before him had a SQL Server which held some of the hotels data.
He did not leave the SA password. How do you find out what the SA password is
if you do not have it? I do not know what to tell him. Does anyone out there
have any idea what he can do?
Thanks in advance for your help.
Dianne
Can someone tell me if there is a password in Entereprise manager.
I've been having trouble with Scheduled tasks.
There are not running anymore.
THANK YOU.
Hi!
When I try to set password for any login begining from 'sa' using Enterpr. Manager or sp_password I get error message:
'Server: Msg 552, Level 16, State 1, Procedure sp_password, Line 63
CryptoAPI function 'CryptAcquireContext' failed. Error 0x80090006: Invalid Signature.'
What does it mean?
Thank you,
Elena.
Is there anyway to "get in the backdoor" if you have installed SQLserver and assigned sa a password (no other users are set up with admin rights) and then forget what the password is.
In the online doc it says you have to reinstall SQL server....isn't there any other way to get in??? For instance in SYBASE you can log on to the console and "go thru the backdoor" and issue a command from there.
Please advise.