The problem does not happen with the [User::RunID] variable or the [Initial Catalog] property of my [RSAnalytics] connection object. But I am not successful in setting the [ServerName] property.
What happens when executed is that the package retains the ServerName property of the deployed package (Value="RTG23SQLDB01UAT1QA"), instead of what I pass in via DTEXEC???
Is ServerName read-only or something? I've tried configuring the package connection to RTG23SQLDB01UAT1PROD and creating a package configuration that I specify in place of the /SET - but to no avail as well!
This package has to work against 10 instances. I really don't want to have to create a separate package for each instance, and then a hack to figure out which one to call.
I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).
For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.
Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.
My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.
I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.
Hello All, Does anybody know how to achive that? <add name="ConnectionString" connectionString="Dsn=dsn_name;uid=user;pwd=password" providerName="System.Data.Odbc" /> I have got this created by the wizard for my sqlDataSource. I am able to get data from tables in the default database specified with the DSN. But I want to go to other database than the default. Thanks a lot in advamce for your help! Kazu
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
In the old DTS, we can use the ActiveX Script to change any task's property programmatically.
Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?
Say, I want to change the Fuzzy look up reference table name.
set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true and have it read OracleNumber's instead of System.Decimal, which can't handle the value
Hello I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display. Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed. For instance in my page load event I would have a CASE statement with numerous SQLString Variables. Here is the current coding for my datasource <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter"> <SelectParameters> <asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" /> </SelectParameters> </asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the SelectCommand="SQLString" but this causes a syntax error Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'. Any help would be greatly appreicated!!
I have a question regarding a Language property on Report, Table and Cell levels. My reports must show monetary values in different formats depending on a currency symbol where the Client resides. For instance, money fields for USA, Canada, UK are shown as 123,456,789.00 and then "$" or "£" symbol; but European countries should have 123.456.789,00 format and a Euro symbol.
I have found that XXX.XXX.XXX,00 format corresponds to the Language property = "Italian". If I set the Language property = "Italian" on Report or Cell level at design time, the report shows the expected 123.456.789,00 format, no problem. (By the way, for some reason, on the Table level this property set does not work at all)
Unfortunately, I was not able to change the Language property to "Italian" on Report or Cell level on-fly using the following expression: =IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"English (United States)","Italian") For debugging, I even tried: =IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"Italian","Italian") But all numbers on the report are still shown in the 123,456,789.00 format regardless the Client's currency symbol.
I don't want to have 2 sets of my reports only because if the monetary format difference. And also I don't want to CAST the monetary value into a string and mask it myself with dots and commas.
I appreciate at advance any help or comment regarding the issue very much. This is a critical bug and it must be resolved ASAP.
I have a SSIS package loading Excel file. The Excel Source automatically give the length of 255 for all text columns. However, some of the column may exceed 255 length.
I cannot change the length of Error output columns. "Error at Data Flow Task [Excel Source [508]]: The data type for "output "Excel Source Error Output" (517)" cannot be modified in the error "output column "F45" (2345)". Error at Data Flow Task [Excel Source [508]]: Failed to set property "DataType" on "output column "F45" (2345)". "
How to change it or truncate it to 255? I am using 64bit VS.
I see various references to the options for a package's protection level (including http://msdn2.microsoft.com/en-us/library/ms141747.aspx) but I can't seem to find anything telling me how to actually look at and/or change the protection level of a package - thus my question is "How do I change the protection level of an SSIS package?"
Hi, please I would like to use something like this>
=IIF(table1_group2 is Collapsed,True,False)
I want to generated some event based on actual state of e.g. report group. if user expand group make this event otherwise (group is collapsed) make another event.
I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
Here are the details:
Environment:
Windows Server 2003 64-bit (Build 3790: Service Pack 2) SSIS 32-bit & 64-bit installed SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
SSIS Package details (compiled in 64 bit)
Script tasks only Microsoft Visual Basic .NET (using TRY...CATCH block) PreCompileScriptIntoBinaryCode = TRUE Run64BitRunTime = TRUE
Execution
Batch file that uses DTExec to execute the Package.
SCENARIO I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
Wrong connection String using 32-bit Execution
While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
Wrong connection String using 64-bit Execution
While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?
P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
Sorry for the lengthy one and Thanks very much for you help in advance .
I have explicitly used Exec SP_dropserver and Exec SP_Addsserver SP's to drop and add a SQL server.
After that i restarted the sql server service. After that, when i used the query
select @@servername, it is returning NULL. But, when I used Select Serverproprty('servername'), it is returning me the correct servername. Can i know as why this behavior is exhibited for the same property of finding servername?
Hi, I'm trying to do a database operation in ASP.NET page using the following code: string connString = "SERVER=localhost;DATABASE=chbr;UID=sa;PWD=password;Connection Timeout=120";SqlConnection sqlConn = new SqlConnection(connString);sqlConn.Open(); string commandStr = "..."; SqlCommand command = new SqlCommand(commandStr); command.ExecuteNonQuery();sqlConn.Close(); But I kept getting the exception saying "ExecuteNonQuery: Connection property has not been initialized." What did I do wrong? Thanks!
Dear all, I'm using ASP.net 2003, I need to display some of the messages that retrieve from a table in my sql server 2k. When I run the program, it works fine, but all of the messages that are supposed to get from the db table were not show. Below is my connection string in web config : <add key="dbconn" value="data source=localhost;initial catalog=MyWeb;Min Pool Size=3;Max Pool Size=20;Pooling=true;password=mypassword;persist security info=True;user id=sa;workstation id=local" /> Any help will be greatly appreciated
Dear all, I'm using ASP.net 2003, I need to display some of the messages that retrieve from a table in my sql server 2k. When I run the program, it works fine, but all of the messages that are supposed to get from the db table were not show. Below is my connection string in web config : <add key="dbconn" value="data source=localhost;initial catalog=MyWeb;Min Pool Size=3;Max Pool Size=20;Pooling=true;password=mypassword;persist security info=True;user id=sa;workstation id=local" /> Any help will be greatly appreciated
Hello, I try to execute the following SP in my class below. But get a runtime error saying "the connection string property has not been initialized" line 11 I have set permissions on the SP to public/execute What is wrong here. My connection string also is below thanks Ehi
1 public class signup_data_entry 2 { 3 public signup_data_entry() 4 { 5 SqlConnection con = new SqlConnection("cellulant_ConnectionString"); 6 7 8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con); 9 command.CommandType = CommandType.StoredProcedure; 10 11 con.Open();
I'm writing my first vb.net app. Have a default page that uses a persons network login to query a database to get all their timekeeper id, firstname, last name, etc. But I keep getting this error. (My code is below) What am I missing??? ExecuteReader: Connection property has not been initialized. 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.InvalidOperationException: ExecuteReader: Connection property has not been initialized.Source Error:
Line 21: conn.Open() Line 22: Line 23: reader = comm.ExecuteReader() Line 24: If reader.Read() Then Line 25: EmployeesLabel.Text = reader.Item("tkinit") <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxxConnectionString").ConnectionStringcomm = New SqlCommand("Select top 1 tkinit, tklast, tkfirst +' '+ tklast as fullname from txxx WHERE login = @login)", conn)comm.Parameters.Add("@Login", Data.SqlDbType.VarChar)comm.Parameters("@Login").Value = Me.User.Identity.Name.Substring(User.Identity.Name.IndexOf("") + 1)conn = New SqlConnection(connectionString)conn.Open()reader = comm.ExecuteReader()If reader.Read() ThenEmployeesLabel.Text = reader.Item("tkinit")FirstLastName.Text = reader.Item("fullname")End Ifreader.Close()conn.Close()End Sub</script>
Hi, I wrote the code below to store images on SQL Server 2005; however, I keep getting this error: The connection String property has not been initialized! My Connection String is stored in the web.config and am using ASP.NET 3.5. 1 using System; 2 using System.Configuration; 3 using System.Data; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.HtmlControls; 7 using System.Web.UI.WebControls; 8 using System.IO; 9 using System.Data.SqlClient; 10 using System.Web.SessionState; 11 12 public partial class CarImage : System.Web.UI.Page 13 { 14 protected void Page_Load(object sender, EventArgs e) 15 { 16 } 17 18 protected void UploadButton_Click(object sender, EventArgs e) 19 { 20 if (Page.IsValid) 21 { 22 Stream imgStream = UploadImage.PostedFile.InputStream; 23 int imgLen = UploadImage.PostedFile.ContentLength; 24 25 byte[] imgBinaryData = new byte[imgLen]; 26 int n = imgStream.Read(imgBinaryData, 0, imgLen); 27 28 String StrCarID = Request.QueryString["CarID"]; 29 int CarID = System.Convert.ToInt32(StrCarID); 30 31 int RowsAffected = SaveToDB(imgBinaryData, CarID); 32 if (RowsAffected > 0) 33 { 34 Response.Write("<BR>The Image was saved"); 35 } 36 else 37 { 38 Response.Write("<BR>An error occurred uploading the image"); 39 } 40 } 41 } 42 43 private int SaveToDB(byte[] imgBin, int carID) 44 { 45 //Store Conn String in Web.Config 46 SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["LocalSqlServer"]); 47 SqlCommand command = new SqlCommand("INSERT INTO tblImage (CarID, Image) Values (@carID, @imgBin)", connection); 48 49 SqlParameter param0 = new SqlParameter("@imgBin", SqlDbType.Image); 50 param0.Value = imgBin; 51 command.Parameters.Add(param0); 52 53 SqlParameter param1 = new SqlParameter("@carID", SqlDbType.Int, 4); 54 param1.Value = carID; 55 command.Parameters.Add(param1); 56 57 connection.Open(); 58 int numRowsAffected = command.ExecuteNonQuery(); 59 connection.Close(); 60 61 return numRowsAffected; 62 } 63 } Any help will be very much appreciated.E
I am trying to create a web form that will be used to create new users. The first step that I am taking is creating a web form that can check the username against a database to see if it already exists. I would it to do this on the fly, if possible. When I execute my current code, I get the following error:
ExecuteNonQuery: Connection property has not been initialized
Below is the code from the page itself: ----- <!-- #INCLUDE FILE="../include/context.inc" --> <!-- #INCLUDE FILE="../include/db_access.inc" -->
<script language="VB" runat="server">
Sub CheckButton_Click(Sender as Object, e as EventArgs)
cmd.ExecuteNonQuery() result = cmd.Parameters("result").Value
If result <> 1 Then CheckResults.Text="<font color=""#ff0000"">Username already exists!</font>" Else CheckResults.Text="<font color=""#009900"">Username is available.</font>" End If
Can anyone see why I might get this error? Here are some more details of the error:
Line 15: cmd.Parameters.Add( "@userName", OdbcType.VarChar, 100 ).Value = Request.Form("userName") Line 16: *Line 17: cmd.ExecuteNonQuery() Line 18: result = cmd.Parameters("result").Value
I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:
Private Sub VerifyNoDuplicateEmail() Dim conn As SqlConnection Dim sql As String Dim cmd As SqlCommand Dim id As Guid sql = "Select UserID from SDCUsers where email='{0}'" sql = String.Format(sql, txtEmail.Text) cmd = New SqlCommand(sql, conn) conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString")) conn.Open() Try 'The first this we need to do here is query the database and verify 'that no one has registed with this particular e-mail address id = cmd.ExecuteScalar() Response.Write(id.ToString & "<BR>") Catch Response.Write(sql & "<BR>") Response.Write("An error has occurred: " & Err.Description) Finally If Not id.ToString Is Nothing Then 'The e-mail address is already registered. Response.Write("Your e-mail address has already been registered with this site.<BR>") conn.Close() _NoDuplicates = False Else 'It's safe to add the user to the database conn.Close() _NoDuplicates = True End If End Try End Sub
Web.Config <appSettings> <!-- User application and configured property settings go here.--> <!-- Example: <add key="settingName" value="settingValue"/> --> <add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" /> </appSettings>
I'm using the SQLCE 3.0 OLEDB Provider via VC++. I cannot seem to find any C++ documentation on using the Local Connection String. We need it to set the Max DB Size.
My primary question is what is the Property-ID for Local Connection String?
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated. Thanks.
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated. Thanks.
I have a package that I plan to run against about 700 databases to look for anomalies. I have several package variables in place that are passed in at runtime. One of them will hold the path and filename of the error log for the current database in process. I want each database to generate it's own error log for documentation and research purposes. However, when I run the package, it continues to use the path and filename that I entered when I created the File Connection Manager. I am trying to update that value in a Script Task by using the ConnectionManager class and setting the value for the "ConnectionString" property. This method is working for the OLEDB Connection Manager (which tells the package which Access database to process), but not for my File Connection Manager. Please help!
I have written a CLR Function in C#. The function works as expected except that I am trying to read data some data during the function call and get the following error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_SLARemaining":
System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
System.InvalidOperationException:
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
I'm creating an xml configuration file to hold the connection string including the password (sql server authentication). My package protection level is set to 'EncryptSensitiveWithPassword'. I set up my connection manager and I check the 'Save my password' box. The 'Test connection' button reports that the connection is OK. I enable the package configurations and I go through the Package Configuration Wizard and I specify 'xml configuration file' and I choose 'Specify configurations settings directly' and I specify a configuration file name with the same directory as the package. I check the 'ConnectionString' property and the 'Password' property and then click 'Close'. Then I save my package changes. Now I look at the xml configuration file in a text editor and I see the Password property has an empty element:
<ConfiguredValue></ConfiguredValue>
Is it supposed to be empty? When I right-click the package in solution explorer and pick 'Reload with upgrade' then I have to enter the password, but the validation fails with 'Acquire connection' error. Should I just be saving the 'Connection string' property, or should I save all the connection elements (i.e. ServerName, UserName, Password etc.)? If I edit the xml configuration file and I type the password into the <ConfiguredValue> element above, and then I do 'Reload with upgrade', then the 'Acquire connection' validation error goes away. Could this mean that I am not able to encrypt the password? Thanks.
I am working with SQL Server 2012. I have deployed a SSIS project that has 2 packages in it. The package connection manager (Test) uses an expression to evaluate one of the Project parameter value (TestConnectionString) to set its ConnectionString property.
This works fine in a Dev environment. However when deployed to UAT, it keeps failing with the error:Â
PackageExport:Error: The result of the expression "@[$Project::TestConnectionString]" on property "Package.Connections[Test].Properties [ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I can not seem to find what the issue could be. I have come across [URL] .... where it says: "If the package contains project parameters, the package execution may fail." but offers no solution.Â
I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.
My expression is as follows:
DataRowsToSkip : @[user:: Var]
where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.
I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.
