Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.
So I want cartProduct = Title and cartCost = Price from the SQL query.
Hi, I'm try to get the query second column value when it is assinged to a varchar variable. Ex:In SP below is statement I wrote SET @Values = (SELECT COL1,COL2 FROM TABLE) Question 1: How to access the COL2 value from @Value2? Question 2: How to access the other row values if above returns more than a row? Please send me the solution as soon as possible. Thanks-Vikash/Bala
I have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount
User::giRowCount is populated by a Row Count Component previously in the Data Flow.
After reading http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 it is very clear that you can actually only use variables in the PostExecute of a Data Flow Script Component or you will get an error "Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute."
What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as second line as part of the header, I also need to parse a read/write variable such as gsFilename to save me hardcoding the path
-they must go in the PreExecute sub --workarounds please-here is the complete script component that creates a file with header, data and trailer --Is there any workaround
Public Class ScriptMain Inherits UserComponent 'Dim fs As FileStream Dim fileName As String = "F:FilePickUpMyfilename.csv" 'Dim fileName = (Me.Variables.gsFilename.ToString)
Dim myFile As FileInfo = New FileInfo(fileName) Dim sw As StreamWriter = myFile.CreateText Dim sbRecord As StringBuilder = New StringBuilder
Public Overrides Sub PreExecute()
sbRecord.Append("RECORD_START").Append(vbNewLine)
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
Public Overrides Sub PostExecute() sbRecord.Append("RECORD_COUNT").Append((vbTab)) sbRecord.Append(Me.Variables.giRowCount.ToString).Append(vbNewLine) sbRecord.Append("RECORD_END").Append(vbNewLine) 'Now write to file before next record extract sw.Write(sbRecord.ToString) 'Clear contents of String Builder sbRecord.Remove(0, sbRecord.Length)
'check to see that student num is in the db Dim connect as SqlConnection Dim strSelect as String Dim cmdSelect as SqlCommand Dim strDbResult as SqlDataReader
connect = New SqlConnection(connStr) strSelect = "SELECT stu_num, stu_fname, stu_lname FROM " + stuTable + " WHERE stu_num=@stuNum" cmdSelect = New SqlCommand(strSelect,connect) cmdSelect.Parameters.Add("@stuNum", txtStdNum.text)
Dim stuFName as String Dim stuLName as String Dim stuEmail as String Dim strRandom as String Dim strPassword as String Dim i as Integer Dim charIndex as String Dim stuMailMessage as MailMessage Dim strHTMLBody as String
In my DB I have a table with the columns stu_num, stu_fname and stu_lname. Each of these columns contains rows with data. However, the proceeding code gives me this error: Invalid attempt to read when no data is present (line 58 --> stuFName = strDbResult("stu_fname") ).
What am I doing wrong here? How do I assign the stu_fname in the DB to the page level variable stuFName?
As a little aside how do I say "If no record was found matching stuNum Then" ... ??
I have a typical scenario here where I am trying to assign a variable a value using another variable, for that I am using two execute SQL task , the fisrt one assigns the first variable a value and then the second one uses the first variable to evaluate and then assigns a value to another variable. The first execute sql task works fine , however the second one fails with the following error
"failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "
Here is the query I am using to assign the value in my second execute sql task
select (CASE
WHEN Sum(col1) <> 0 AND ? = 'M' THEN 'M'
WHEN Sum(col2) <> 0 AND ? >= 'T' THEN 'T'
WHEN Sum(col3) <> 0 AND ? >= 'R' THEN 'R'
ELSE 'S'
END) as Current
FROM dbo.TABLE1
I am passing the variable by mapping the parameters and then assigning the value using a single row result set. Can someone please tell me where am I going wrong?
Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.
So, I declare a variable: DECLARE @variable int Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.
I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.
I would like to assign the value of a variable to a column name of a table. For eg:
declare @sam varchar(100) set @sam = 'Insert' create table #temp(Sample varchar(100) not null) insert into #temp(Sample) Values(@sam) drop table #temp
I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.
1 20031012121212 200 (recordtype, CreationDateTime(YYYYMMDDHHMISS), Rec_Count) -- Header records 2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record 2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record
For the above given sample data I am having two outputs at Condition Split (based on the recordtype). I want to store the 1st record datetime value into a variable and then I want to use that variable value into 2nd and 3rd row.
Basically, detailed records would be stored into the database not the header record. Is there any way I can use the variable while doing processing for 2nd and 3rd records.
Hello all, I'm creating a stored procedure in SQL Server 2000. I'm trying to count how many rows match a certain WHERE clause, and assigning this integer to a variable:
---------------- DECLARE @RowCount int,
SELECT COUNT(*) FROM Customers WHERE FirstName='Joe' ----------------
How can I assign the count that returns from the SELECT statement to the @RowCount?
I need some help regarding a conversion in a Script Task.
I am reading my variable values from a database with a sql task, the table has two columns, variable and variableValue. Looping through the recordset and setting the different variables works well, with two links: http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx
setting the variable value only works well if the package variable is defined as string, because the db field is a varchar, trying to assign an integer for example brings up an error.
Therefor I tried something like CType: Dts.Variables("MyVar").Value = CType(MyRecordsetField,String), where the target datatype should be depending on the variable datatype instead of being assigned as a constant.
I have a smalldatetime field in a table that holds dates as 3/1/2008. If you run a select query on the field it will bring back the date as a datetime vairable. I reran the query using the following: select convert(varchar,cast((max(ENTRY_DTE))as datetime),101) This query brings me back the date as 03/01/2008.
When I utilize the same query and set the value to a declared variable, defined as datetime, it brings me back the date as : Mar 01 2008 12:00AM
I have tried several different ways to convert the date however as soon as appoint it to a declared variable I receive the same result!!
I've asked this question before and I've read the answers before and I still cannot get it to work. My task is simple, I want to use the execute sql task container to grab a value from a database and put it in a variable. I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select output_location as output_location from script_master
Result Set is set up like this:
Result Name: output_location ; Variable Name: User::output_location
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "output_location": "The type of the value being assigned to variable "User::output_location" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
I don't know what I'm doing wrong, I've followed all the instructions exactly on how to populate a variable in this container. My variable is set up as a string, if I change it to object I can get it to work. I think this is because the object is allowing nulls. I really believe that the variable is not populating and that is why I'm getting errors.
Please help. If you could provide step by step example's that would really make my day.
How to assign multiple values to emp1 variable separated by comma Ex: If ajay karthik vijay are employee names in emp table then emp1= ajay,karthik, vijay set emp1= (select employeename from emp) returns error error: subquery returns morethan one value
Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?
Any thoughts?
In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?
In my SSIS package I am looping multiple flat file to load data into target table. I have used one variable to get that filename. I have error log table which used to log the invalid record which come from various files.
All are working fine , except I want to log the filename also in my error log table. How can we use the package variable to assign the variable value in script component.so that I can map the output column to target table. I have created Filename column in script component in output column tab.
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
hello I have a problem with Sql task when sql task tried to assing a value to my variable I have this error ""La valeur n'est pas comprise dans la plage attendue." I'm using ODBC connexion for a csv file
[Execute SQL Task] Error: An error occurred while assigning a value to variable "JDETimezone": "Unable to find column Timezone in the result set.".
i know what the problem is i.e. no row is returned then what is the problem
here you are.... i want to it work... strange... ok i explain...
actully i have some processign to do with variable JDETimezone even no row is returned.... can u tell me the alternative to do the follwing task...
I want to retrieve a record from some table and do some processing and if no row is present or returned then i want to do seperate processing.... can ne one help me out ?
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
I have a table with first name, last name, SSN(social security number)and other columns.I want to assign group number according to this business logic.1. Records with equal SSN and (similar first name or last name) belongto the same group.John Smith 1234Smith John 1234S John 1234J Smith 1234John Smith and Smith John falls in the same group Number as long asthey have similar SSN.This is because I have a record of equal SSN but the first name andlast name is switched because of people who make error inserting lastname as first name and vice versa. John Smith and Smith John will haveequal group Name if they have equal SSN.2. There are records with equal SSN but different first name and lastname. These belong to different group numbers.Equal SSN doesn't guarantee equal group number, at least one of thefirst name or last name should be the same. John Smith and Dan Brownwith equal SSN=1234 shouldn't fall in the same group number.Sample data:Id Fname lname SSN grpNum1 John Smith 1234 12 Smith John 1234 13 S John 1234 14 J Smith 1234 15 J S 1234 16 Dan Brown 1234 27 John Smith 1111 3I have tried this code for 65,000 rows. It took 20 minute. I have torun it for 21 million row data. I now that this is not an efficientcode.INSERT into temp_FnLnSSN_grpSELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,(SELECT 1 + count(*)FROM distFLS AS c2WHERE c2.ssn < c1.ssnor (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =substring(c1.fname,1,1) or substring(c2.lname,1,1) =substring(c1.lname,1,1)or substring(c2.fname,1,1) =substring(c1.lname,1,1) or substring(c2.lname,1,1) =substring(c1.fname,1,1)))) AS group_numberFROM distFLS AS c1JOIN tu_people_data AS c3ON (c1.ssn = c3.ssn andc1.fname = c3.fname andc1.lname= c3.lname)dist FLS is distinct First Name, last Name and SSN table from thepeople table.I have posted part of this question, schema one week ago. Please referthis thread.http://groups.google.com/group/comp...6eb380b5f2e6de6
I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
