Hi,
In c# - how to pass uid,pwd,dbname and servername as input parameters from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.
Thanks,
Shanthi
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
I apologize in advance for posting yet another connection failure issue. I went through quite a few posts and could not find the actual answer so here is the issue. I have a main SSIS package to call five other packages. This seems to work fine in my BIDS workstation; however, when I copied it, including the bat file that was created by dtexecui utility, to the production environment (which runs on 64 bit - probably has nothing to do with the 64 bit platform), the main package executes fine but the child packages failed with this error:
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "An error has occurred while establishing a connection
to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote conne
ctions.".
It seems that the child packages are still using the old connection strings from my workstation. My question is how can I pass the connection string from the batch file to all the child packages that the parent (main) package is using.
Thanks,
Andrew
We were able to use a dynamic connection string in the report designer, but once we deployed to the report server we are getting the following error:
Error during processing of the ConnectString expression of datasource €˜Dynam€™. Has anyone experienced this, and how did you fix it?
Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group€™s connection to Oracle. All subsequent connections to Oracle will use the group€™s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.
View 7 Replies View RelatedI need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.
The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.
I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.
I had tried embedding the DB in the website folder but it would overwrite the customers database.
i have successfully implemented a dynamic connection string based on a dropdown list of environments (dev, test, prod). it works well during testing in the vs2005 ide; but once i deploy it to the rs server, it complains that the credentials are not stored in the rs server database and won't run the report.
as in most large organizations, the developers do not have control over the rs server, so i cannot manipulate rs config or web config files on the server side; so, how do i get past this obstacle?
thanks in advance
Hi,
Is there any way to Dynamically change(Based on User login) Connection String for a report data source from code behind. In My application each user may have different data base. I am using a single shared data source for all the report. Please give me a solution.
Thanks
Sonu
This method has worked beautifully for all my SSIS pkgs thus far.
Basically, I use a Script Task to derive the name of the newest file in a local directory. Then I save the name of the file to user a user variable, e.g. User::File.
Then, in my flat file properties > Expressions, I set "ConnectionString" to reference User::File.
However, when attempting to use this method with an Excel source, I get this error message:
Error at myPkg [Connection manager "Excel Connection Manager"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at myPkg: The result of the expression "@[User::Folder]+ @[User::File]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
HELP......... I need this to work!
Thanks
I have a package where I need a dynamic connection string for an Analysis Services connection manager.
I have implemented this successfully for a Text data source, and a SQL data source, but the same approach does not seem to be working for an AS connection.
I set some expressions for the AS connection manager (ServerName, InitialCatalog, even the entire ConnectionString itself), but they don't take. I don't get any errors, but the task processes the cubes for the AS connection as it was established at run time. The design time connection string changes don't appear to get evaluated. This seems to be an issue only for AS connections.
Let me know if you have any ideas - thanks.
Has anyone been able to find a way to use a stored procedure with a dynamic connection string?
Hi, I'm a newbie to SSRS, and was wondering if anybody can shed light on a problem I have. I have a report which every client uses, but each client's data is held in its own database. Rather than create many reports, is it possible to create 1 report, which all can use, passing in the different datasource? I was thinking of a hidden parameter, passed by URL. Or maybe using the report viewer control in VS2005. Can anybody please help?
Thanks
Dan
Hi all,
I am able to set dynamic source for the text file(flat file) but i want to set the connection string (file name) to excel source dynamicaly. I have tryied lots of time by taking a variable in foreachloop container . Variable is itself able to pick the file name dynamicaly but whern i am tying to set connectionstring to excel source it gives error.
Steps that i have done: -
1) Drag foreachloop container
2) set directory,FileNameRetrieval,FileSpec
3) Made VariableMapping
4) Now drag a dataflow task in the foreachloop container
5) select excel source
6) When i am selecting varaible as connectin string from properties of excel connectin manager, i am getting this error : -
TITLE: Microsoft Visual Studio
------------------------------
Error at Package3 [Connection manager "Excel Connection Manager 2"]: An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Please help me, whats the problem? can i set connectionstring via variable to excel source
Hello,
Kindly give me the solution ASAP how to do Dyanmic Connection in ExcelConnection manager.
Thanks
Thiru
Hi,
View 3 Replies View RelatedI'm trying to setup a dynamic ole db connection using the SA user ID, it has to be dynamic because the server name will change and it has to be SA because we're pulling information from system databases that some users don't have access to.
If I setup a regular static connection using SA credentials it works like a charm of course. When I create an expression to use the User:erver variable it doesn't work, it throws an error message saying that "The login failed for user sa" among other things, I'm thiking that the sa's password is not being saved.
Where exactly do I place a password for dynamic connections using sql server users? On the connection string? On the password property of the source? Any ideas?
Hi All,
The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?
Thanks,
Manoj Kumar
I am facing a reporting service issue.
System information:
1. Our Reporting Server installed on a DB server.
2. We have one windows application which executes on the same server, which generates the reports snapshots.
3. One set of reports with single Shared data source
Scenario:
We have many countries for which report history snapshots needs to be generated. The report information is stored in different databases named as database_CountryCode on the same DB server.
Questions:
1. We need to dynamically change the shared data source connection string to point to the respective country database, when generating the report for that particular country. --> We found out this can be accomplised by using parameterized connection string in report specific data sources in SQL 2005. Can we achive something similar in SQL 2003 Reports as well?
2. We also need to instantiate the reporting service web service in multiple threads, one for each country, where in each thread picks up the corresponding country code, changes the connection string and generates reports snapshots. Can this be accomplished? I know this goes against the entire idea of licensing, but my question is just about feasibility of this idea.
Addnl Info
The CreateReportHistorySnapshot method of reportingservice.asmx returns a snapshotID, which is the timestamp of report snapshot creation. We tried creating the same report snapshot for the same country in 2 threads. Whenever the timestamp for both snapshots was same, only one report snapshot actually got created. Can this be overcome?
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
Hello.
I have packages that must generate log errors dynamically including time of execution and the name of the task.
I make it changing the properties of the connection inserting two expressions.
1.-I alter the File Usage Type to 1 to generate this files.
2.- I alter the connection string as: @[User::myvariable] +"constant_description"+ time description+ +".txt"
The time description is :
(DT_STR,40,1252) DAY (GETDATE())+"-"+(DT_STR,40,1252) MONTH( GETDATE())+"-"+(DT_STR,40,1252) YEAR( GETDATE())+" + REPLACE( (DT_STR,10,1252) (DT_DBTIME) GETDATE(),":","_")
But it is not the problem
In those packages I have one connector for all the tasks and in execution time it creates one file for each of the tasks.
The problem is the way I insert in the filename the task name.
I have a pre-execute event handler in each task that modifies a string variable( myvariable) appending the task name. When I execute de package it works great but when I only execute a task, the program do not enter in the event and do not put the task’s name.
How can I put that name without using that handler? There is another handler can I use to do it that happens before the system generates the new file name and after pre-execute? Anyone knows another way to do this kind of things?
hi, i have this sql statement:
@uname varchar(20)
select@sql = 'select user_name, password, role_code, expiry_date,effective_from,active from usermaster where userid='1' and username like'@uname%'
it is not worrking for me.i don't know how to pass values in dynamicsql,can any one answer for me please.
I have an SSIS package that has a variable called @RUN_DATE.I would like to create a sql agent job and uses the set values tab to pass a dynamic parameter to the ssis package. for example, i would like to always set the variable to the last day of the previous month. I know that If i hardcode the date as 5-31-2013 it works fine, but i have not been able to figure out how to make the value dynamic by using an expression or if this is even possible.
View 4 Replies View RelatedI am trying to use one sql store procedure but don't know how to pass the parameters. I am posting here my store procedure:-
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
[Code] ....
My table looks like below:
emp_id, time_code, date, time_charged
RB, VAC, 20130222, 8
RB, HOL, 20131128, 8
RB, VAC, 20130311, 8
My output should be:
emp id VAC HOL
RB 16 8
Hi,
I use a DataGrid to show the data, and I want it has a sorting and Paging function,
so I use dataset to collect the data from middle tier function and stored procedure.
I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
CREATE Procedure Ruying_AutoSearch10
(
@Make varchar(50),
@Model varchar(50) = NULL,
@Condition varchar(20) = NULL,
@Miles float,
@Zipcode varchar(5),
@SortExpr varchar(100)
)
AS
DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND a.Model = IsNull(@Model,a.Model)
AND a.Condition = IsNull(@Condition, a.Condition)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
ORDER BY @SortExpr
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO
but I got the error as "variables are only allowed when ordering by an expression referenceing
a column name". How I fix this error? Please help.
Thanks.
Lin
Hi,
I have a parent package which accepts date as input. I can configure Set Values of Execute Package Utility with hard coded date value and it works fine. My question is how do I configure Set Values to accept dynamic date value or current date value (may be using GetDate()) ?
Thanks in advance.
Prabha
I'm running a query on Oracle using OPENQUERY.
I need to pass a parameter.
Which means I'm having to use dynamic sql.
In turn this requires a ##table due to the connection issue of using EXEC.
There is surely a better way of doing this, but what?
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.
Is this an intrinsic issue?
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else
Thank you.
My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime
dtToday = DateTime.Today
'Create connectionDim con As New SqlConnection(_connectionString)
'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd
.Connection = con
.CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here
.CommandType = CommandType.Text
'Add Parameters.Parameters.AddWithValue("@UserID", UserID)
.Parameters.AddWithValue("@Today", Today)
End With
'Return DataReader
con.Open()Return cmd.ExecuteReader()
End Function
Hi,
I am migrating one of my DTS package to SSIS.
My task is to read the filename from a database table and transfer the flat file data in to a table.
In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?
In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source.
Any help ?
Thanks,
Ravi
Help! I'm trying to figure out how to pass a string variable to my sqldatasource's "selectcommand" attribute. I'm trying to construct my SQL dynamically to adjust some things. My code is something like this,
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="_Default" EnableEventValidation="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><%@ Import Namespace="System.IO" %><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"></head><body style="font-family: Arial,Verdana; font-size: 8"> <% Dim selectCommandVar as String = "select column from mytable" %>
<form runat="server"> <asp:SqlDataSource ID="SqlChild" runat="server" ConnectionString="<%$ ConnectionStrings:mynnectionString %>" ProviderName="<%$ ConnectionStrings:gmConnectionString.ProviderName %>" selectcommand="selectCommandVar"> </asp:SqlDataSource></form></body></html>
What's the best way to get the contents of my selectCommandVar variable into the SelectCommand attribute of my sqldatasource? The above syntax doesn't work and I've tried several permutations that do not work either. The examples of sqldatasource I keep finding have the SQL statement hardcoded in the <asp:sqldatasource> section, which won't work for me. HELP!
Hello,
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString
Thanks for your help.
The following query works perfectly (returning all words on a list called"Dolch" that do not contain a form of "doing"):SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "doing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)However, what I really want to do requires me to piece two strings together,resulting in a word like "doing". Any time I try to concatinate strings toget this parameter, I get an error.For example:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "do' + 'ing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)I have also tried using & (as in "do' & 'ing") and various forms of singleand double quotes. Does anyone know a combination that will work?FYI, in case this query looks goofy because of the unused "CombinedLexicons"table, it is because the end result should be a working form of thefollowing...Figuring out the string concatination is just a step toward this goal:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')WHERE (dbo.CombinedLexicons.vchWord IS NULL)Thanks!
View 2 Replies View RelatedHi,
I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.