I have looked to no avail...
Anybody see a dynamic way to set the seed in the Row Number Transformation? I do not see any expressions property and the only property that takes a variable is the FINAL number...
I would have though that a very common use would be to get the maximum IDENTITY from a table and use that value (after adding one) as the starting number for the rows being imported...
For a project currently in development, one of our developers would like to use DTS to perform the following actions. However we both have only just begun to work with this tool so we are somewhat inexperienced with DTS and looking for guidance.
We need a way to generate multiple output rows from a single input row.
For example, the input record contains the following fields: RSN1, RSN2, RSN3, and RSN4.
I want to generate four output records.
one record containing RSN1,
one record containing RSN2,
one record containing RSN3 and
one record containing RSN4.
Could DTSTransformStat_SkipFetch be used in this situation?
If someone could provide us with a sample or some suggestions on how best to do this, it would be greatly appreciated.
Sincerely,
Michael Sinnott
Wausau Benefits
I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.
My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.
right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)
I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.
David
Hello
i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?
there are no errors while executing this transformation
but this function returns a value
Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation
Thanks
MS SQL 2000 sp4 on WinXp Pro SP2I am very new to this so please let me know what I can do to make iteasier for you to understand the problem.I have a non delimited text file. This text file has several columnsthat for the most part are fixed length but..The fixed format starts with a variable length number( char 10) as thefirst column and a max (char 30) description field and another (char50) long description field.if the (char 30) description field has a measurement ( ie 4.5" ) thenthe first field stats with a ". The subsequent description will endwith a "" or double quote. The fields can only work if the doubleqoutes are replaced with a single space and then the leading singlequote is deleted. If this is done in this order then the fixed fieldlengths work.example of actual data***************This example does not have any quotes in it*****1015304 Sof Sand Block Holder RUDOLPHINTE BH y N N nbrd EA 1.00000 .83 1.25This example does have the quotes**********"10154 Rud Zeb NonDisf Cush Cur ve 180Gr 7"" RUDOLPHINTE DUZ180F5 y N N nbrd EA 1.00000 .43 .65"This is the next several lines***************1015401 Rud Blk Disf Cush Curved 100/180Gr RUDOLPHINTE DUB100/1 y N N nbrd EA 1.00000 .46 .691015402 Rud Blk Disf Cush Curved 100Gr RUDOLPHINTE DUB100F5 y N N nbrd EA 1.00000 .46 .691015405 Rud Myl Disif Violet 100 Gr RUDOLPHINTE GLMM100F y N N nbrd EA 1.00000 .43 .651015406 Rud Myl Purple Disf 80Gr RUDOLPHINTE GLMM080F y N N nbrd EA 1.00000 .43 .651015407 Rud Myl Light Green Disf 120Gr RUDOLPHINTE GLMM120F y N N nbrd EA 1.00000 .43 .65"1015408 Rud Myl Yellow Disf 180G r 7"" RUDOLPHINTE GLMM180F y N N nbrd EA 1.00000 .43 .65"end of example*******************right now I am doing this by hand in notepad and then importing toexcel to generate a csv file. Can anyone suggest a better way to dothis or just point me to an example.Thanks very much for any
View 2 Replies View RelatedHi All,
I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
Venkatesh.
i've created a package that will copy data from an oracle table to a sqlserv table (that table elemenst are identical), when i click on the connecting line between the two connections it executes without any errors, but nothing is copied, when i try to execute the package i'm getting an error... where can i go to find out what's causing the error. There is no error message or number returned from dts, all i get is a red 'x' . There are 1236 records that need to be inserted and when i get the red 'x' it tells me 1236 records have been processed. When i click on tranformations and select test, it works, but since it's a test nothing is actually copied. I've got other packages that i've created that do the same thing with other tables and they work. I tried just copying one record and that worked, so i assumed it must be data dependent, i've checked all the fields and made sure they weren't null, i've checked to make sure there aren't duplicate primary key records. Without knowing what the actual error is I'm stumped ???
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies View RelatedI have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View 3 Replies View Related1. how to show page number & total page number in report body?
2. how to show total records number?
I want to format a number like "#,##0.00" in order to handle it in Excel as a number (i.e. compute a sum).
Excel is able to show a number in a specail format and still allow to compute with ...
Thanks in advance,
Peter
I am testing something in Visual Basic
that talks to a database and I want to
filter results by -> field1 like "###".
However, that 'like' and '#' is VB syntax.
How do you say that in SQL?
i am an asp programer trying to program in aspx c#
i have a litle problem geting the id number from my sql db and put it into an intiger
what i am doing is that i am trying to make the picture have the same name as the id number in the db
String sql = "SELECT id ";
sql += " FROM PictureLibrary";
sql += " ORDER BY id desc";
int pictureNR = ??
I have tryed to find a way to include the row number in a querry but without result.
I have a table select some rows ordered and now I will add a column called rank
How do I get the rownumber in the field rank ?
Thanks in advance
Jan O
Hi,
I need to add row number to my query result and I know how to do that in SQl2005 but the row number function is not working in 2000.Please help me to figure out how should I do that in SQl2000.The query that I have is kind of a big query with 162 columns. It is coming from another view.
Thanks.
Sep
Hi all,
Does anybody know why SQL server gives the wrong weeknumber when I enter the following syntax in my query analyzer.
select Datepart (wk,getdate())
It should give week 46, but it gives 47
What is wrong ?
Are the settings of SQL server wrong
It does give the correct day, month and year.
Thanks in advance
Is there a way to take the row number from my code and put that into a column I have code that returns the top 50 of sales and I want to put those row numbers into columns that they coordinate with.
View 6 Replies View RelatedHow to generate a seq number in TSQL. My need is for every recordinsert ,I need to insert a seq number paded with letter 'I'.Please help me.Adv Thanks mate--Posted via http://dbforums.com
View 3 Replies View RelatedAll I am trying to do is return the row number (calculated field?)with each row returned from an SQL query. I think I have done this inthe past but am a bit rusty after not using SQL for a while. Ifanyone could help with a code snippet this would be gretlyappreciated.
View 3 Replies View RelatedCan someone tell me why I'm not getting .9375?
declare @MyFloat1 float
set @MyFloat1 = 30/32
select @MyFloat1
returns 0
declare @MyFloat2 real
set @MyFloat2 = 30/32
select @MyFloat2
returns 0
declare @MyFloat3 decimal(2,2)
set @MyFloat3 = 30/32
select @MyFloat3
returns 0.00
update [Costing].[dbo].[Supplier]
set n =
(
SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )
as RowNumber from [Costing].[dbo].[Supplier] as t
where t.suppliercode = Supplier.suppliercode
)
hi i cannot update a column with the row number,
it is all taking 1001, supposed to be 1001,1002 and so on .
thanks.
Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.
I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.
Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.
I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?
Thanks.
Richard M.
G'day,
I have a table with a primary key being a bigint and its set to auto increment (or identity or whatever ms calls it). Is there anyway I can get the ID number that will be assigned to the next Insert before I insert it? I want to use that ID number within another field when inserted.
I hope that makes sense.
Thanks for any help.
Robbo
I have a string in form "abcdefg 12355 ijklmn"Now I want get only the number 12355 within the string !!Is there any function available in T-SQL of Sql server 2K??Thanksfor any help
View 6 Replies View RelatedI have a few stored procedures that returns @@error. If I understand correctly, @@error should be set to 0 if there is no error. However, my procedures are returning 1 and 2 yet working just fine as far as I can tell. There is not exception popping up in SQL Profiler either. What does this mean, and is it something I should be concerned about?
View 7 Replies View RelatedI need to change a number that looks like 1365 and make it look like 1,365 without any decimal or trailing zeros. Then number can even be a 7 digit number. It seems like the only way I know is the money to varchar conversion using style 1, but this adds trailing zeros and a decimal point. Can anyone help on this
Thanks,
Hemal
Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ?
10x,
Clara