Using Pack.Conf. SQLServer To Store Source Conn. Str. But Its Not Working.
Feb 21, 2008
Problem:
i have configure a SSIS package in BIDS to use an Env. Var. to store the location of the .dtsconfig file for the connection to my SSIS configuration table in SQL. I than use another Env. Var. to retrieve the source conn. str. from my SQL SSIS configuration table. Once my package loads, i select a simple sql task to execute, but it comes back and says that it cannot make the connection. How does the SQLServer connection string know what password to use? This is where i'm confused. I believe it loads the connection string for my source database but it doesn't get the password. I just started working in SSIS and i'm trying to get my head around all of the dynamic options of SSIS. I appreciate your time and help.
Problem Details:
package A has 1 sql execute task
Env.Var. 1 stores the location of the .dtsconfig file that stores the connection data to ssis sqlserver configuration table (used to store additional package properties in sql server)
Env. Var. 2 stores the connection string of my source database.
package protection level set at "EncryptSensitiveWithPassword"
the sql task is assigned to the source connection string,right click and execute task.
here is the error:
Failed to acquire connection "source database" Connection may not be configured correctly or you may not have the right permission on this connection.
I'm executing this from within BIDS just to check that my setup is going to work.
Here are some items i've tried or troubleshoot:
1. If I roll back the 2 package Configs or disable using package configs and replace my original source connection manager and create a new connection manager than i can get the sql task to successfully execute again. It won't usually work by just disable the package configs. i had to delete the existing source connection manager.
2. create a new package trying the same steps as above.
3. change the package protection level to "encryptSensitiveWithPassword" expecting this to retain the source connection password. I tried a variety of package protection levels.
I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.
I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.
I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?
I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)
I found out that I could use SQLDMO to enumerate objects in a SQL server. However, I can't connect to my server, even though I have both "windows and SQL server" authentication on "(local) Windows NT".
Here is the code (it does not contain the validation in this snippet):
Imports System.Data.SqlClient Imports SQLDMO
Public Class ADO_2 Inherits System.Web.UI.Page Protected WithEvents txtQuery As System.Web.UI.WebControls.TextBox Protected WithEvents txtresults As System.Web.UI.WebControls.TextBox Protected WithEvents lstDB As System.Web.UI.WebControls.DropDownList Protected WithEvents lblResult As System.Web.UI.WebControls.Label Protected WithEvents cmdExecute As System.Web.UI.WebControls.Button Protected WithEvents cmdNext As System.Web.UI.WebControls.Button
Private m_objConn As New SqlConnection() Private m_objSQLServer As SQLDMO.SQLServer
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
EnumerateDatabases()
'TEMP lstDB.Items.Add("Northwind") lstDB.Items.Add("Pubs") 'END TEMP End Sub
Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
m_objConn.Close() 'Just in case..... End Sub
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim objCommand As SqlCommand Dim objDataReader As SqlDataReader Dim intField As Integer
objCommand = New SqlCommand(txtQuery.Text, m_objConn) objDataReader = objCommand.ExecuteReader
While objDataReader.Read txtresults.Text &= vbNewLine For intField = 0 To objDataReader.FieldCount - 1 txtresults.Text &= objDataReader(intField).ToString.PadRight(15) Next End While
m_objSQLServer = New SQLDMO.SQLServer() m_objSQLServer.Connect("radu")
'Iterate through all databases on the target server: For Each objSQLDatabase In m_objSQLServer.Databases lstDB.Items.Add(objSQLDatabase.Name) Next objSQLDatabase
EnumerateDatabasesExit: objSQLDatabase = Nothing m_objSQLServer.Close() m_objSQLServer = Nothing Exit Sub
EnumerateDatabasesError: lblResult.Text = Err.Number.ToString & ", " & Err.Description Resume EnumerateDatabasesExit End Sub End Class
Why can't I connect with m_objSQLServer.Connect("radu") ? I also tried m_objSQLServer.Connect("radu", "", ""). Otherwise, I can connect just fine with m_objConn.Open() (after I select a db from the dropdownlist, which is for now populated by hand)....
Do any one has a step by step instructions for installing the service pack 2 and 3 on Active/Passive cluster. I installed sqlserver 7.0 in one active/ passive cluster and it worked successfully. Now I followed the instructions as given by microsoft by running the cluster wizard. It removed the virtual server. I made all sqlservr services offline.
When I try installing I couldn't able to install as it is saying "Active virtual server is there run clustered wizard in all the nodes". Now I am in the middle of no where.
I have Windows XP Professional Version 2002 Service Pack2.
I Have:
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
Service Pack is failing to Install CLIENT COMPONENTS; message is:
Product : Client Components Product Version (Previous): 2047 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log Error Number : 29549 Error Description : MSP Error: 29549 Failed to install and configure assemblies c:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894 Error message: The system cannot find the file specified. Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
This situation, I am guessing, also preventing me to run import/export from the SQLServer Client.
Is it possible to create an indirect Package Configuration by using the /CONF switch when running a package via dtexec. Here is what I would like to do: 1. Use an Indirect SQL Server configuration type 2. Instead of storing the connection string in an env. variable, I want to specify it at run time using the /conf switch
In other words, are there any alternatives to storing the configration connection string on the server?
I have this select statement: SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR [title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch + "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%" + strSearch + "%')
This statement works fine but when I turned it into a store procedure, it returns nothing. I created the store procedure as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE sqlSearch -- Add the parameters for the stored procedure here @strSearch varchar(250) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR [title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" + @strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR [content_nws] LIKE '%" + @strSearch + "%') END GO
How can i Store files like (abc.txt , abc.doc) in SqlServer Database from ASP.NET application ?? my files are about (2 Mb ) size aslo want to know about text as well as .jpg , . gif files also pls send me code ... help me
I have a question about where package is stored if you select sqlserver or SSIS package store (msdb). Is both of these going to the same place sysdtspackages90. On one of my systems I was saving as sqlserver but when users created a job they where using SSIS package store msdb. This seems to be working, though I would have thought that they were to different locations. On another system when I did same thing the job failed, and looked like maybe it was running wrong version, but when I resaved project as SSIS package store(msdb) then it did work. Even though I had earlier saved that package under sql server. So on 1 system it looks like they are the same on the other system I am not sure.
So if you can clarify for me what the difference between the two areas are if any?
i have this trigger in my database : ALTER TRIGGER dbo.AddVoucher ON dbo.User_AddVoucher AFTER INSERTAS SET NOCOUNT ON;DECLARE @UserId int, @Add_id int, @voucher_id char, @Kredit money, @date smalldatetime, @last_balance money, @voucher_status charSELECT @UserId = UserId, @voucher_id = Voucher_ID, @Add_id = Add_id, @date = Deposit_Date FROM InsertedSELECT @Kredit= Voucher_Value, @voucher_status = Voucher_Status FROM Voucher WHERE Voucher_ID = @voucher_idINSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date) VALUES (@Add_id,@UserId, 'Kredit',@date)select @last_balance = Balance from User_Balance WHERE UserId = @UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @UserId order by Update_Id DESC) if (@voucher_status = 'active') -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- Insert statements for trigger hereBEGIN update User_Balance set Balance = @last_balance + @Kredit where AddVoucher_ID = @Add_id update Voucher set Sold_Date = @date where Voucher_ID = @voucher_idENDELSE BEGIN raiserror ('Voucher is not valid',0,1) rollback transaction ENDgo the problem is the update function is not working and the if statement always put to 'FALSE' do you think anything wrong with the code
Hi allWe have some tables with a couple of layers of very simple views built ontop. In the table are maybe 6 columns and about 15000 records. The firstview cobines the data in the table with some other data from a lookuptable. The second view does some sorting on the first view using certaindates . They have worked fine for well over a year now.Until this morning that is... the views stopped returning the full set ofresults- even the very simple one that sits just above the table. The viewreturned the core of the data from the main table, but nothing from thelookup table.In order to get them to work we had to delete each view (using access frontend to do this), and then recreate it with exactly the same SQL text. I amguessing this causes SQL Server to recompile it. As soon as view 1 had beenrecreated it worked, but view 2 still failed, again rebuilding view 2 itstarted working.The only thing I can think of is that this morning I added 2 new fields tothe base table, but I'm sure I've done this before without any (noticable)problems.any thoughts as to why it happened would be welcome, I am a bit nervousnow...thanksAndy
Hello, I am wondering how it is going to work using SQL Server configuration type to store connection string in the SQL Server table. How does SSIS know what database to connect to if its connection string is store in the database? Thanks
- Operating System Minimum Level Requirement (Success) Messages * Operating System Minimum Level Requirement
* Check Passed
- Operating System Service Pack Level Requirement. (Success) Messages * Operating System Service Pack Level Requirement.
* Check Passed
- SQL Server Edition Operating System Compatibility (Warning) Messages * SQL Server Edition Operating System Compatibility
* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
* The current system does not meet the minimum hardware requirements for this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online.
* Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
- Internet Explorer Requirement (Success) Messages * Internet Explorer Requirement
* Check Passed
- COM Plus Catalog Requirement (Success) Messages * COM Plus Catalog Requirement
* Check Passed
- ASP.Net Version Registration Requirement (Success) Messages * ASP.Net Version Registration Requirement
* Check Passed
- Minimum MDAC Version Requirement (Success) Messages * Minimum MDAC Version Requirement
* Check Passed
furthermore i get error 26 when trying to create new sql server database. and while doing the SQL server surface area configuration i got this message.:
SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC) ------------------------------ Program Location: at Microsoft.SqlSac.MainPanel.FormFeatures..ctor(String machineName, Form callingForm)
HOpefully somebody out there will help me...or is this problem only appearing in sql server 2005 and would it be a better decision to install SQL server 2000...or if someone can help me rectify this problem i'll be really thankful.
I have created a report parameter called ServerName. However, when I deploy the report and report viewer opens, I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Error during processing of the ConnectString expression of datasource €˜AdventureWorks€™. (rsDataSourceConnectStringProcessingError)
Is there some really obvious step I am missing here? The relevant parts of my rdl are below.
Thanks in advance
<DataSources>
<DataSource Name="AdventureWorks">
<ConnectionProperties>
<ConnectString>="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks"</ConnectString>
Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source. I select the SQL Command in the Data Access Mode, then use:
USE [SecurityMaster] EXEC [dbo].[smf_ListEquity]
It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:
Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider. Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.
I am using sqlconnection to connect sqlserver 2005, Before connect the server i want check the server is exists or not, if server is not exist give a prompt to user enter the actual server name, If there is any way to check whether server exists or not pls
I want to import data from a remote MySql Database
So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:
Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a] ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a] ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a] ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a] ...... bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults) bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing) bei System.Data.Odbc.OdbcDataReader.Close() bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData() bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)
the sql string is very simple, this should not be the problem:
I'm using the XML Source to process a hierarchical set of XML. As such, the XML Source creates keys to maintain the hierarchy. This is very convenient, and keeps me from having to invent my own keys.
The problem is that the datatype of these keys defaults to DT_UI8. Which SQL Server 2005 datatype should I use to store these values in my staging tables? BIGINT corresponds to DT_I8, which can't accept DT_UI8 values.
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via CONVERT(varbinary(max), 'test text') then there is no problem, I get results as expected.
However if I populate the column via CONVERT(varbinary(max), CAST('test text' as nvarchar(max))) no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers, Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
-- Create test database CREATE DATABASE FullTextTest GO USE FullTextTest GO
-- Create test data table CREATE TABLE TestTable ( pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY, varbinarycol VARBINARY(MAX), documentExtension VARCHAR(5), ) GO
-- The below single entry WILL BE FOUND (the text source is being entered directly) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value) INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html') INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html') GO
-- Create the full text catalog sp_fulltext_database 'enable' GO CREATE FULLTEXT CATALOG TEST AS DEFAULT GO CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033) KEY INDEX tablePK GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')
I want to connect to SQLExpress database on the network. I can do so using code but I want to use the Data Source Configuration Wizard. It seems this only works for local instances of SQL server. Is this true?
I can connect just fine to the database on the network using Microsoft SQL Server Management Studio Express...for what this is worth.
I have tried to map a drive to the SQL server machine and pick the mdf file but I get the error msg of network path is not supported for database files....I have turned on allow remote connections, allow tcp/ip, named pipes, added sqlserv.exe and sqlbrowser.exe to my firewall exceptions ect..
I want to be able to use Table Adapters, SqlDataAdapters ect. After reading numerous forum questions/ kb articles this seems to be a common problem. I am on the verge of just saying forget it and use MS Access instead.
Hi forum, thought about time I updated from OleDB to SqlClient. My remote host company supports MS SQL Server 2005, I have created a testsql.mdf, now I want to connect to that database,( im used to writing Access conn like below and have updated my OleDB code to Sql ). <%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.OleDb" %> strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\inetpub\vhosts\blablabla\httpdoc\App_Data\testdb1.mdb"; How do I write the SQL connection string? <%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.SqlClient" %> strSqlConn =?????? this bit ????? This is the remote host path LocalSqlServer data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true This my first SQL data application so need some help thanks Paul
Hi All - Prior to ASP .Net I use to create a connection object for SQL Server in my global.asa file and I would reference the obj whenever needed. how can I do the same in asp .net. Here is my old asp sample:
Global.asa file: Sub Session_OnStart set session("conn") = createobject("adodb.connection") session("conn").Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxxxx_db;UID=xx;PWD=xxxxxxxx;" End Sub Call from Other Pages: sqlstr = "select * from members WHERE memberid = '" & session("mbr_memberno") & "'" set objRS = session("conn").Execute(sqlstr) ... How can I do this in .net?
I have downloaded some code on how to upload images to a sql database and I am getting this error message
"SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied."
I have opened Enterprise Manager and made sure I was a "user" and everything seems to be ok there. The connection string looks like this "server=localhost;uid=sa;pwd=;database=ImageUpload" Could anyone give me an idea on what to check or try next
When I moved my SQL server to another machine within my LAN, it stopped functioning. server=(192.168.1.3)\DB;IntegratedSecurity=True;uid=;pwd=;database=AspNetDb
Could someone kindly give me some advice? I appreciate your assistance.
Does anyone know how to create trusted connections?. What I want to do is to have connection to a sql server that's in a different domain as I am (a NT workstation). I tried to create a login id on the server with my nt id but got an error:
Hi Forum, I am having issues with hosted Sql05 server conn. Ive made the move from Oledb to Sql, so am unsure if trouble is permissions or connection related! Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. <connectionStrings> <remove name="LocalSqlServer" /> <add name="ConnectionString" connectionString="Data Source=**.**.**.**;Initial Catalog=***_Database.mdf;Integrated Security=True;" /> </connectionStrings> <system.web> <roleManager enabled="true" defaultProvider="SqlRoleProvider"> <providers> <clear /> <add name="SqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ConnectionString" /> </providers> </roleManager> Ive tried some conn varients re security id, but its always the same error. I can use SQL Server Management Exp to login/manage database, using SQL authentication. The web app is the conn I cant open, hosting company support useles, now thats something new, cheers P
