DTS Tranformation Question
Feb 2, 2001
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
View 2 Replies
May 3, 2006
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...
View 6 Replies
View Related
Dec 21, 2006
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
View 7 Replies
View Related
May 22, 2007
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
View 3 Replies
View Related
Jan 24, 2007
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 Related
Oct 11, 2007
Hi 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.
View 1 Replies
View Related
Jan 2, 2008
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 ???
View 4 Replies
View Related
