I recently started a short topic about staging an incremental load, and the answer was to use a variable. I've been fiddling around with it have run into a question about calling the variables.
I've gotten my "Execute SQL Task" to generate a single row result set and store it as a global / package Variable. Now I'm not sure how to call / use this variable when I'm loading data from an ADO.NET Data Reader Source.
Do I need to map the variable to a parameter first? Can I call the variable in the SELECT statement like this?
Code Snippet
SELECT *
FROM source
WHERE date > @variable
I was reading an MSDN page on Using Variables in Packages where they recommended the following syntax:
Code SnippetSELECT * from Production.Product WHERE ProductID = ?
But I'm not sure how to get the variable or parameter mapped to my Data Reader Source.
Any help would be greatly appreciated.
Thanks in advance,
In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .
I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .
Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...
Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa
Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?
The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.
The SQL command in the custom properties doesn't cross reference back to the variable.
If anyone can help or needs further detail to help please post a resonse.
As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------
[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
and also this:
[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.
I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected. It seems that somehow the 3 columns are not read in from the source file? ans alslo fiscal year, fiscal week is not set up up properly in my data destination? anyone faced such errors before?
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
I am having a very wierd issue regarding a DB2 sql query. I need to get data from Db2 and insert into our sql server database. Using data flow task, to get data I am using the data reader source. COnnection is ado.netodbc connection. THis sql query also has some comments in it.
The first wierd thing is...
1. On Development server, when I run this query manually, meaning using toad, winsql (connection to the db2 database), the query runs fine. Brings back approx 667 rows which is correct. ON the same server when I try to run this query, via a SSIS pkg, data flow task, using data reader source, gives me error on those comments that exist in that query. But if I run the same SSIS pkg on another server (Integration server). It runs fine. The same pkg also runs fine if I run it from my machine. SO What is different on my Dev server compared to the Integration server.
2. Say if I take those comments out from the sql query, then try to run the ssis pkg. The query is stuck at the first record and goes in an infinite loop mode. though my query is not a procedure, it is just a sql statement. But this ssis pkg with the query runs absolutly fine on the other server. I aslo tried using the other types of connection and ole db source but still the same problem on the Dev server.
What do I need to look for that is so different on the dev server compare to the INT server. I also checked the version on both these server for Visual Studio 2005(by going to About Microsoft Visual Studio), it is the same.
This is what I have on both the servers.... Microsoft SQL Server Integration Services Designer Version 9.00.3042.00
i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:
how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:
select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.
I have declared a variable XYZ in Parent package Similarly I have declared ABC in Child package and have done the configuration. I have assigned some value to XYZ How to get the value in Child Package.
I'm calling a procedure on a remote Server (local SQL2005, remote SQL2005) and I need the return value.
Local: declare @value int execute ('exec mbtest1.dbo.psybcis ?', @value OUTPUT) at [REMOTESQLSERVER] select @value
Remote: create procedure [dbo].[psybcis] (@value int OUTPUT) as begin select @value = '13' end
I do not get a value in the OUTPUT variable - just NULL. Documentation says: Execute a pass-through command against a linked server { EXEC | EXECUTE } ( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ] [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] ) [ AS { LOGIN | USER } = ' name ' ] [ AT linked_server_name ] [;] There is an OUTPUT parameter, but how does it work? Is my syntax wrong?
It works when using following syntax, declare @value int exec [REMOTESQLSERVER].mbtest1.dbo.psybcis @value OUTPUT select @value but I need to use this procedure call in a distributed transaction to a Sybase ASE server and this syntax is not allowed for cross-system-calls. So first I want to get it work from SQL2005 to SQL2005.
is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)? ------------------------------------------------- ex: create sp1 as
select * from tbl1 select * from tbl2 ------------------------------------------------- how can i use a data reader to read the items from tbl2?
Hi Guys,I have a quick question about DataReader, I have a function called "ExportTotal" i am calling this function from another function. what it does is it will put the Total for each Network in the Line 34. Right now what it is doing is It is putting the First Networktotal,2ndNetwork total,3rdNetwork total....... in the line 34. what i want is if the control comes to first network then it has to put only 1st network total and for the 2nd network only 2nd network total and so on. Please see my function below. Can you guys tell me what i am doing wrong?ThxPrivate Function exporttotal() As String Dim sql As String Dim dbFunctions As New DatabaseUtilities Dim tempinvoicetotal As String 'Dim rateactuals As String Dim filetext As String Dim tempclientname As String Dim strconn As String Dim prev_network As String = "" Dim current_network As String = "" strconn = CONNECTIONSTRING sql = "SELECT CAST(SUM(tblSpot.rateActual) AS int(4)) AS Rateactuals, SUM(tblSpot.rateActual * 0.85) AS netrate, SUM(tblSpot.rateActual * 0.15) AS commrate,TBLCLIENT.CLIENTNAME " & _"FROM tblSpot INNER JOIN tblContract ON tblSpot.fkContract = tblContract.pkid INNER JOIN " & _ " tblClient ON tblContract.fkClient = tblClient.pkid WHERE tblSpot.fkContractType = 'UNWIRED' AND " & _ "fkInvoiceNumber = '" & Me.txtinvoicenumber.Text & "' GROUP BY TBLCLIENT.CLIENTNAME" Dim myConn As New SqlConnection(CONNECTIONSTRING) Dim myCommand As New SqlCommand(sql, myConn) myConn.Open() Dim dbreader As SqlDataReader = myCommand.ExecuteReader() While dbreader.Read() Try Dim Rateactuals As String If dbreader("Rateactuals") Is DBNull.Value Then Rateactuals = "" Else Rateactuals = dbreader("Rateactuals") tempinvoicetotal = Rateactuals End If Dim clientname As String If dbreader("clientname") Is DBNull.Value Then clientname = "" Else clientname = dbreader("clientname") tempclientname = clientname End If If prev_network = "" Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else current_network = tempclientname If prev_network <> current_network Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else End If End If Catch SqlEx As SqlClient.SqlException Session("Error") = SqlEx.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) Catch Ex As System.Exception Session("Error") = Ex.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) End Try dbFunctions = Nothing End While myConn.Close() End Function
I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
Hi All, I got an error while running this code.There is already an open DataReader associated with this Command which must be closed first How can I resolve the error?
protected void Page_Load(object sender, EventArgs e) { string sql; SqlConnection Connection = new SqlConnection("ConnectionString"); sql = "SELECT PO_SE_Line_ID FROM PO_STOCK_QUERY_LINE_DETAILS WHERE TRANS_NUM ='TR-A-00-01-93'"; SqlCommand command = new SqlCommand(sql, Connection); SqlDataReader Dr; Connection.Open(); Dr = command.ExecuteReader(); while (Dr.Read()) { sql = "SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93' and PO_SE_Line_ID=" + Dr["PO_SE_Line_ID"].ToString(); SqlCommand command1 = new SqlCommand(sql, Connection); SqlDataReader Dr1;
Hi, i dont know whats gone wrong! but all of a sudden it seems to be throwing an error, i have looked at my previous code and it matches exactly when it was working, here is the code below int i = 0;for (i = 1; i <= 3; i++) {
//This gets the stock ID from the textbox.string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text;
//This is the sql statement.string sql = "SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open(); //This is a reader for the results to go in.SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); //This sets the title label text to the value of the description column.TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i); string strtxtDesc = currentBox1.Text;strtxtDesc = dr["title"].ToString();
}; myConn.Close(); i = 0;
the error its throwing is this CS1519: Invalid token '(' in class, struct, or interface member declaration for the line myConn.Open() does anybody have any idea how to solve this? Jez
Hi Guys,I have a quick question about DataReader, I have a function called "ExportTotal" i am calling this function from another function. what it does is it will put the Total for each Network in the Line 34. Right now what it is doing is It is putting the First Networktotal,2ndNetwork total,3rdNetwork total....... in the line 34. what i want is if the control comes to first network then it has to put only 1st network total and for the 2nd network only 2nd network total and so on. Please see my function below. Can you guys tell me what i am doing wrong?ThxPrivate Function exporttotal() As String Dim sql As String Dim dbFunctions As New DatabaseUtilities Dim tempinvoicetotal As String 'Dim rateactuals As String Dim filetext As String Dim tempclientname As String Dim strconn As String Dim prev_network As String = "" Dim current_network As String = "" strconn = CONNECTIONSTRING sql = "SELECT CAST(SUM(tblSpot.rateActual) AS int(4)) AS Rateactuals, SUM(tblSpot.rateActual * 0.85) AS netrate, SUM(tblSpot.rateActual * 0.15) AS commrate,TBLCLIENT.CLIENTNAME " & _"FROM tblSpot INNER JOIN tblContract ON tblSpot.fkContract = tblContract.pkid INNER JOIN " & _ " tblClient ON tblContract.fkClient = tblClient.pkid WHERE tblSpot.fkContractType = 'UNWIRED' AND " & _ "fkInvoiceNumber = '" & Me.txtinvoicenumber.Text & "' GROUP BY TBLCLIENT.CLIENTNAME" Dim myConn As New SqlConnection(CONNECTIONSTRING) Dim myCommand As New SqlCommand(sql, myConn) myConn.Open() Dim dbreader As SqlDataReader = myCommand.ExecuteReader() While dbreader.Read() Try Dim Rateactuals As String If dbreader("Rateactuals") Is DBNull.Value Then Rateactuals = "" Else Rateactuals = dbreader("Rateactuals") tempinvoicetotal = Rateactuals End If Dim clientname As String If dbreader("clientname") Is DBNull.Value Then clientname = "" Else clientname = dbreader("clientname") tempclientname = clientname End If If prev_network = "" Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else current_network = tempclientname If prev_network <> current_network Then filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"Session("EDIExport4") = filetext prev_network = tempclientname Else End If End If Catch SqlEx As SqlClient.SqlException Session("Error") = SqlEx.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) Catch Ex As System.Exception Session("Error") = Ex.Message.ToString Response.Redirect("Error.aspx?Form=" & Request.Path) End Try dbFunctions = Nothing End While myConn.Close() End Function
WMI Data Reader Task: WmiConnection = WMI Connection Manager WqlQuerySourceType = Direct input WqlQuerySource = "SELECT Name FROM CIM_Datafile WHERE Name = 'C: est.txt'" OutputType = Property value OverwriteDestination = Overwrite destination DestinationType = Variable Destination = User::WmiVariable
I've tried setting the WmiVariable to both String and Object data types. I've tested the WMI connection (both within SSIS and through a sample VBS script), and that works just fine.
Hi , I have a situation where i need to read data from one table and and the result that i get i pass it thru a where clause in another query for which i need to read the database again. Problem iam phasing is with the Sqldatareader in a while loop . When iam trying to open a SqlDataReader with in a SqlDataReader iam getting an error that i need to close the data reader that i have already opened. Is there a way to solve this problem Code: public void fnIduction() { //Calculating the Deduction of Individual Employees //Select from the EmployeeMast tableSqlCommand cmdEmp2 = new SqlCommand("select EmpCode,EmpName,DesigCode,DeptCode,BankCode,PFNo,PanNo,BankAcNo,GS,ESINo,PayrollGroup,LICNo,CategCode from EmployeeMast", cn); cn.Open();SqlDataReader dr02; dr02 = cmdEmp2.ExecuteReader();while (dr02.Read()) {strEmpCode = dr02["EmpCode"].ToString(); //Select from MondedPay,Deduction tables (Variable Deductions or Monthly Deductions)SqlCommand cmdDed02 = new SqlCommand("Select MondedPay.EmpCode,MondedPay.DedPayCode,Deduction.Name,MondedPay.DedPayMonth,MondedPay.DedPayYear,MondedPay.DedPayAmount from MondedPay,Deduction where Deduction.Code=MondedPay.DedPayCode and EmpCode='" + strEmpCode + "' and MondedPay.mType =1 ", cn);SqlDataReader drDed02; drDed02 = cmdDed02.ExecuteReader();while (drDed02.Read()) { strMonDedNam = drDed02["Name"].ToString();dobMonDedAmt = Convert.ToDouble(drDed02["DedPayAmount"].ToString());if (intDedPer == 1) { dobMonDedAmt = (dobDedAmt * dobBasic) / 100; dobMonDedTot = dobMonDedTot + dobMonDedAmt; } else { dobMonDedTot = dobMonDedTot + dobMonDedAmt; } } drDed02.Close();
Can anyone provide an example or point me to an article that explains how to programmatically set the SQL command for a data reader. I am using a data flow task in a For Loop Container and want to change the SQL command for each loop based on the data from the previous.
maybe you can help me with a little SSIS task. I want to fetch some wmi infos from serveral servers with the wmi reader. to read something is no problem, but whats the best way to bring them to a table or the control flow? Read from the generated file cant be a way. I also tried the variable. How do I get them to data flow. I tried a script task but i cant read the resultset. What format has the variable? ado.net, ado? Whats the best way to get the results into a table?
I am using a Ado Connection Manager to connect to a M S Access source.
But when I use this connection Manager in Data Reader Source, I am Not able to Configure Data reader Source. It gives exception "Cannot Acquire Managed Connection From Run Time Connection Manager".
Hello, I have a datareader and whenever the value that it is suppose to display in the Label is empty because there is no Data Entered Yet I gives me and error and I have the event set ONLOAD so the page with no data to disply does not load. So the Ranking.text = a select statement i have, but when I dont have any data into the SQL database there is nothing to SELECT so the program gives of an error. Can someone help me with this. Thank you. Here is the code: Dim objReader As SqlDataReader conn.Open() objReader = comm.ExecuteReader() objReader.Read()lblRanking.Text = objReader("Rating") objReader.Close()
I am query event log information for a single day using WMI Data reader task and it is taking for ever. I am querying event log for remote servers but the server exectuing the process and queried servers both are in the same domain.
I've created some store procedure for use with Insert. This works great. Now I'd like to be able to use Select statement so I can get the data in some textboxes.
Right now I use Select with SqlCommand and DataReader. This works fine, but I'd like to learn with Stored Procedure with this.
What do I put in the stored procedure to return found records and how what code should I use after executenonquery?
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
Hi I'm currently trying to create a few DTS packages that Import some very wide tables, Im using an ODBC data source into a Data Reader. I want to re-direct any errors into an error destination. The problem I've got is that I can go in and set each column to redirect but was hoping I could select or specify that this needs to happen for all the rows? Does anyone know if this is possible and if so how I go about it?
I am running a sql task which will pass table as object variable to the result set
I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.
Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.
can someone tell me if this is possible (var1,var2,... are variables in the package scope)
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
In several weeks of effort and research I cannot locate any method to get this to work and need some help. We've loaded the most recent version of the Microsoft OLE DB (Feb 2007) and have located several discussion threads on these and other forums that all point to the same conclusion... you cannot use this to talk to a UDB/DB2 instance on Sun Solaris unless you are pulling a full table. Any custom SQL will not work. So, I'm trying to setup a custom query which in most cases is to determine a date to use in the filter (where clause) and the rest of the query is static. The DataReader source does not support parameters and also appears to not support SQL command from variable. Any thoughts on how to get this to work without having to read the full table and pass the data to subsequent filters within the package???
