Extract, Transform , Load
Apr 2, 2008im new to vb.net so i will like sum help with extracting data from a database and loading it into another database using vb.net coding. Thanx for helping guys.
View 1 Replies
ADVERTISEMENT
Extract And Load From And To Oracle
May 3, 2007is it possible to extract from Oracle, transform (preferably in the buffer) and load data into a Oracle db using SSIS? is there any white paper/ article on the process?
View 1 Replies View RelatedExtract And Load In Flat File
Nov 6, 2007
Hi,
I have a dataflow task which has 3 oledb source objects connected to each data conversion object and these are connected to a union all and finally to a flat file destination.
The purpose of this one is to extract data and pump them to the flat file.
If i run this in production during the time users are doing transactional processes (typical, add, edit delete), will it have an impact?
cherriesh
Integration Services :: Extract Data From Server And Load Into Oracle Database?
Jun 23, 2015Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.
View 5 Replies View RelatedNot Able To Load The Application In Case Web Farm Garden When We Load Data Through Background Thread.
Dec 14, 2007Hi,
Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.
Please help us.
Ajay Kumar Dwivedi
Load All Data Without Knowing Old One Was Load In The Previous Time???
Apr 27, 2007I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
DTS Transform
Jul 13, 2004I get this error:
column 6 ('CheckDate', dbType_dbtimestamp), status 6: dat over flow
invalid character value for cast specification
Here is the sql generated :
CREATE TABLE [DMS].[dbo].[Master] (
[Date_Entered] smalldatetime NULL,
[Initials] nvarchar (10) NULL,
[VendorName] nvarchar (50) NULL,
[CheckNum] nvarchar (20) NULL,
[ExpenseType] nvarchar (10) NULL,
[CheckDate] smalldatetime NULL,
[CheckAmount] money NULL
)
IM guessing i need to change date_enteed type or do a cast
any help pls
!~<M
Look UP Transform
Apr 10, 2008
Hi ,
i am using to Lookup transform for the matching records , if my source records are matching with lookup records it work fine , if not it through the following error
[Lookup [34]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Lookup" (34)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (36)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Please advice , how i can solve this .
Thanks in Advance
Raja Ragothaman
Load A SSIS Package Via Web Service: The Package Failed To Load Due To Error 0xC0011008 Error Loading From XML.WHAT IS THAT?
May 19, 2006Hello,
I have a big problem and i'm not able to find any hint on the Network.
I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)
I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.
I imported the dtsx file inside my "Stored Packages".
I would like to load and run the package programmatically on a Remote Scenario using the web services.
I created a solution with web service and web page that invoke the web service.
When my code execute:
Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)
I got the Error:
Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
The error message doesn't help so much and there is nothing on the www to give me and advice....
Is it a SSIS problem???
Thank you for any help!!
Marina B.
DTS Transform Issue
Jun 28, 2006I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.
Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?
If there is could you be specific in how to setup the DTS transformation script.
TIA
Jeff
Transform XML From A SELECT...FOR XML
Jan 14, 2008I want to transform elements in a XML-respons.
If I use FOR XML PATH, ELEMENTS in the SELECT-statment I get the following result:
<row>
<nr>99</nr>
<namn>test bolag</namn>
</row>
<row>
<nr>02</nr>
<namn>test bolag 2</namn>
</row>
I want to modify the elements so that they look like this insteed in the SELECT-statment.
<row>
<field name="nr">99</field>
<field name="namn">test bolag</field>
</row>
<row>
<field name="nr">98</field>
<field name="namn">test bolag 2</field>
</row>
I need to transform the resulting XML because I need to send it to a webservice with a mySQL database on the reciving end.
Please help me :-)
Data Transform
Jun 22, 2008Hi friends,
Can somebody tell me how to do this-
How can we Analyze existing code used to transform data into the Operations Data Warehouse, and make changes to correspond to upcoming changes in the SAP data sources.
Thanks
sk
Transform Recordset
Jul 20, 2005Hi,I have table where i have the following fields in datasheetview:id | date | image | question1 | question2 | question..NI would like to have it in this way:id | date | image | questionnr | answer1 01-01-2004 test.tif 1 1000 (this is the value offield question1)As you can see the first 3 fields remains the same, but the records of those3 fields should be inserted for each record of the question fields. I can dothis with a union query for each questionfield, but.....How can i do this automatically, because there are more than 500 columns.
View 1 Replies View RelatedScript Transform
Jun 25, 2006Hi,
My requirement is to check whether value of a particular column is null or not. if it is null I have to enter warning messages into the temp table I have created.
For this I am using Script Transform
Now I want to know how to write info from script transform to a table using SSIS.
Currently I am using the following code in script component
[Code]
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim ConnString As String
ConnString = "Data Source=ABC;Initial Catalog=XXXX;Integrated Security=SSPI;"
Dim sqcn As New SqlConnection(ConnString)
''Dim sqlCmd As New SqlCommand(ConnString, sqcn)
'sqcn.Open()
if Row.Col1_IsNull Then
sqlCmd.CommandText = "Insert into AuditLog values('ERROR','Missing','" + Row.Col5 + "','" + Row.Col6 + "') "
sqlCmd.ExecuteNonQuery()
end if
End Sub
[/Code]
Without using SqlConnection and SqlCommand is thereany way I can get the Connection Obj and able to insert rec in the table.
Thanks
OLE DB Command Transform
May 8, 2007Hi All ,
I am creating packages from a template package whicg I have built ,I have managed to implement basically everything sucessfully .Setting Properties on all the different tasks ,Connections etc except for the OLE DB Command transform.
I have not been sucessfull in getting to the properties or collections which allows me to do the mapping of the Command to parameters (Command Below),I am aware that the command executes for every row . I really need help with how to now do the mapping between the columns and the Paramaters programmatically in c#
I have set the sql command properties of the OLE DB Command Transform,as below
//Setting Update Comand ComponentProperties
IDTSComponentMetaData90 oledbCMDUpdate = dataflow.ComponentMetaDataCollection[0];
oledbCMDUpdate.Name = "name" ;
oledbCMDUpdate.RuntimeConnectionCollection[0].ConnectionManagerID = pack.Connections[0].ID;
CManagedComponentWrapper instanceCMD = oledbCMDUpdate.Instantiate();
instanceCMD.SetComponentProperty("SqlCommand", GetUpdateSQL(tablename)) ;
The Sql that is returned by the GetUpdateSQL(tablename)) method is below
UPDATE [ADM_AdjustmentAction]
SET AdjustmentActionCode = ?
,AdjustmentActionName = ?
,AdjustmentActionDescription = ?
,AdjustmentActionEFD = ?
,AdjustmentActionETD = ?
,UserID = ?
,ProcessDatetime = ?
,ModuleID = ?
WHERE AdjustmentActionID = ?
Thanks in Advance
Cedric
TRANSFORM Query
Aug 31, 2006I'm new to SQL Server 2005. I used the TRANSFORM query in Access to display the data I had stored in columns into rows. I want to do something similar in SQL Server 2005 but it doesnt let me. I have used the same query here, but it gives an error.
This is the query I was running in Access and it was working:
TRANSFORM Max(Schools.Expense) AS MaxOfExpense
SELECT Schools.[FiscalYear], Max(Schools.[FIPS]) AS [Total Of FIPS]
FROM Schools
GROUP BY Schools.[FiscalYear], Schools.[FIPS]
PIVOT Schools.[DataID];
If there some other syntax for SQL Server or it doesnt support this command or what???
Need Help With LOOKUP Transform
Aug 22, 2007I have a very simple problem I am trying to solve.
I have a table with a "DateEntered" field, and I have an ssis pkg set up to load data from a file into the database table. I just want to make sure that no one loads the same file twice in one day.
For example, if today is 8/22/07, and "DateEntered" is "2007-08-22", then I want to add a Lookup transform to run a query that will check and see if there's any rows in the table with a "DateEntered" is "2007-08-22". If so, don't load the file again!
Here's my query:
SELECT Code
FROM myTable
WHERE DATEADD(dd, DATEDIFF(dd, 0, DateEntered), 0) = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
(all the dateadd stuff is doing is removing the time portion from the DateEntered field, so we are comparing apples to apples).
Now, if the query returns a bunch of "Codes" then we know that the data has already been entered for the day! So far, so good.
Now, how do I set up the Lookup to get it to work? I'm getting this error message:
Error 1 Validation error. Data Flow Task: Lookup [1299]: The lookup transform must contain at least one input column joined to a reference column, and none were specified. You must specify at least one join column. FXRateLoader.dtsx 0 0
But I thought I did this! On the columns tab, I have:
Lookup column: code
Lookup operation: Replace 'code'
Output alias: code
I have my error output set to:
Lookup output - redirect row
I don't know what I'm doing, obviously..........!
Need assistance :-)
Lookup Transform
Jan 24, 2007Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?
Need Help On Lookup Transform
Jun 25, 2007Please suggest me an article that describes 'Lookup Transform' (Specifically passing parameters) in detail.
Note: Is it possible to pass "variable" as parameters in Lookup transform?
CLR SP To Transform Xml (SecurityException)
Feb 27, 2006I am in the process of creating a simple managed stored procedure using C# and VS2005. The goal is to transforms an Xml document with a Xslt file that may contain a very simple script .
<msxsl:script language="C#" implements-prefix="user">
<![CDATA[
public static string Lower(string value)
{return value.ToLower();}
]]>
</msxsl:script>
At this stage my stored procedure code is extremly simple.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Manufacture(string xsltFile, string xmlFile)
{
XslCompiledTransform xslt = new XslCompiledTransform(false);
PermissionSet ps1 = new PermissionSet(PermissionState.Unrestricted);
XmlSecureResolver resolver = new XmlSecureResolver(new XmlUrlResolver(), ps1);
xslt.Load(xsltFile, XsltSettings.TrustedXslt, resolver);
....more code....
xslt.Transform(xmlFile, xslArg, ms);
}
If I execute the SP above and the script exists in the Xslt file, I get the following error upon loading the Xslt file. If I remove the script it transforms perfectly.
A .NET Framework error occurred during execution of user defined routine or aggregate 'Manufacture': System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at System.Xml.Xsl.Xslt.Scripts.CompileClass(ScriptClass script)
at System.Xml.Xsl.Xslt.Scripts.CompileScripts()
at System.Xml.Xsl.Xslt.QilGenerator.Compile(Compiler compiler)
at System.Xml.Xsl.Xslt.QilGenerator.CompileStylesheet(Compiler compiler)
at System.Xml.Xsl.Xslt.Compiler.Compile(Object stylesheet, XmlResolver xmlResolver, QilExpression& qil)
at System.Xml.Xsl.XslCompiledTransform.CompileToQil(Object stylesheet, XsltSettings settings, XmlResolver stylesheetResolver)
at System.Xml.Xsl.XslCompiledTransform.LoadInternal(Object stylesheet, XsltSettings settings, XmlResolver stylesheetResolver)
at System.Xml.Xsl.XslCompiledTransform.Load(String stylesheetUri, XsltSettings settings, XmlResolver stylesheetResolver)
I have tried messing around with CAS and giving virtually everything full trust. Nothing resolves the issue.
Any ideas?
LookUP Transform...
Apr 16, 2007Dear friends,
I have a ETL that have a Lookup transform to get a rate from a table SpotRates.
The problem is when the match od some date in SpotRates Table doens't exist...
And for that records I need to lookup for next date...
For example...
SpotRate Table
Date
Currency
Rate
05-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2262
06-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2312
07-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2179
10-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2099
11-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2105
12-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2125
13-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2094
18-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2252
19-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2346
20-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2346
21-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2315
24-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2365
25-04-2006 0:00
DOLAR ESTADOS UNIDOS
1,2425
When I first try to lookup the date 17-04-2006, doesnt give me any records... and I need to create a new lookup for the next date from 17-04-2006. And in this example the next date is 18-04-2006.. How can I do it??
I made a sql query date gives me the next date with 2 parameters ... but I'm having some errors...
SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ?) AND (Data > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC
In this exampple, the parameters returned from lookup1 is:
Currencies_name= 'DOLAR ESTADOS UNIDOS'
DATE='17-04-2006'
I need to create a second lookup transform to return the next date/currency for each row that didnt match in the first lookup...
Regards,
Pedro
Lookup Transform
Aug 10, 2007I want to do something relatively simple with SSIS but can't find an easy way to do this (isint it always the case with SSIS )
I have a column lets say called iorg_id, and I want to lookup the matching rows for this col in a table.
In this table iorg_id may have several potential matching rows. In this table there is another col called 'Amount'.
I want to retrieve for each iorg_id the matching iorg_id in the other table but only the row with the largest value in the 'Amount' col.
I couldn't find a way to do this all in the Lookup Transform.
I can match the iorg_ids and retrieve the Amount column, but can't find a way just to retrieve the matching row with the largest value in the Amount col. The only way I can think to do this is then run the output from the Transform through an Aggregate function and determine the Max (although haven't tested this yet).
Seems strange to me in that the SQL in the Advanced tab gives me something like:
select * from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?
where I believe the first 'select *' is retrieving all the cols that are listed in the LookupColumns list in the Columns tab.
I thought I would be able to amend this to something like:
select max(amount) from
(select * from [dbo].[Table1]) as refTable
where [refTable].[iorg_id] = ?
but I get a metadata type error.
So, questions are:
Is it possible to do this all in the Lookup Transform are do I have to use the Aggregate function as I think ?
Why is it not possible to amend the sql in the Advanced tab to manipulate the returned data ?
Pivot Transform Help
Sep 5, 2007I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.
I need to go from this...
PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y
into this....
A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y
Can I do this using the pivot transform? Any suggestions?
TRANSFORM && PIVOT Equivalents?
Jan 16, 2003I am trying to find the equivalent to MS Access's TRANSFORM and PIVOT in T-SQL. I've tried using GROUP BY with CUBE, and I can't seem to get the data in the correct format. Can someone help? And please explain things to me like I am an idiot, because I am.
Here is the current table and the desired results that I want.
Current Table
MonthTypeSubtypeTotalTime
1TaskASubA5
1TaskASubB10
1TaskASubC8
1TaskBSubX5
2TaskASubA4
2TaskBSubX5
Desired Result:
TypeSubTypeJanuaryFebruary
TaskASubA54
TaskASubB100
TaskASubC80
TaskBSubX55
Thanks.
Colleen
DTS - Datapump, Transform Data
Feb 24, 2001Hi,
On my MS SQL Server 2000, I am trying to create a generic way to load tables into my datawarehouse.
I have as input to the process a large number of table definition(s) stored individually as files on my server. And, ascii delimited data files in various locations but mostly accessible via NFS mounts.
I created two DTS package in MSSQL2K that in theory represents what I want to do:
package1
... invoke package2 with global variables to load a system of related tables
package2
... check for a trigger file
... set the "Execute SQL Task" statement to my first file
... run the "Execute SQL Task" which drop/add's a table
... set a "Connection" to a data source file that I want to use
... run the transformation
and, with that my package starts to fall apart
... set the "Execute SQL Task" statement to the next file, and
...... goback and execute it
I can't figure out how to set the table in the transformation section to the table I want to use. And, I assume next to have the transformations links between the source and new table relinked.
The source files contain in the first row the column names as found in the tables I just created.
thanks,
Dave Rowsome
Transform Variable - Add OR Operator
May 1, 2015I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO
TRANSFORM MUTLIPLE COLUMNS
Jun 18, 2008Hi all, new to SSIS so please bear with me on the noobie question:
Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor.
Need to copy the data to a new database and tables
Then for each table:
1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order.
What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rows
Any Ideas would be appreciated! Thanks!
How To Transform Fact Table Only By SQL?
Jul 20, 2005Hi,this is easy with OLAP tools, but I need to do it just with MS-SQLserver:fatTableyeartypeval97a197b297c398a498b598c6....yeartype_atype_btype_c971239845699...The problem is number of different types - not just 3 like a,b,c butmore than 100, so I don't want to do it manually likeselectyear, a.val, b.val, c.valfrom(select year, val from factTable where type='a') afull join (select year, val from factTable where type='b') bon a.year = b.yearfull join (select year, val from factTable where type='c') con a.year = c.yearis it possible somehow with DTS or otherwise? I just need to presentthe data in spreadsheet in more readable form, but I cannot find anyway how to export the result from MS-SQLserverOLAPservices to Excel...Martin
View 2 Replies View RelatedLookup Transform + Composite Key
Oct 27, 2007I am trying to digest this logic, and have been unsuccessful so far. I am designing a package for incremental loads, but the destination table has a composite primary key on 2 columns, one of which is nullable. The source data comes from a SPROC. Uptill now, I have been banging my head trying to get this logic to work via the Lookup transform with a conditional split, but it doesn't work. Am I on the right track, or should I be using the SCD Wizard?
As a side note, I have been trying to work a solution using Andrew's blogpost on doing incremental loads: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
Thanks.
Error Using Pivot Transform
Jan 5, 2006Hey, did someone try to use the pivot transform?
It's not what you would call easy to configure...
In BOL there is the following section:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm
I have followed the instructions there and I get an error when I try to map the new output column to the pivotkey:
"Output column "Yellow" (69) cannot be mapped to PivotKey input column"
So I mapped the new output column to the column holding the quantity values. This worked.
May this be a bug in BOL?
Further on I got some strange behaviour of the pivot transform:
My input is the following CSV-Flatfile:
sk1;sk2;pk1;qty
A;1;Yellow;2
A;1;Green;1
B;1;Yellow;5
A;2;Blue;3
A;2;Green;9
A;1;Green;5
If I omit the last line, everything is fine.
When passing the file as is into the pivot transform the output is:
SK1 SK2 Yellow Green Blue
A 1 2 1 NULL
B 1 5 NULL NULL
A 2 NULL 9 3
A 1 NULL 5 NULL
As you can see the key a;1 is duplicate. The cols SK1 and SK2 are my primary key of the destination table.
When the input is sorted by the first two colums the pivot transform throws an Error:
Error: 0xC02020CF at Pivot w sort, Pivot [39]: Duplicate pivot key value "Green".
I expected to have a sum over all Green for the key A;1. It seems that I have to use an aggregate which in my opinion should
be obsolete here.
As a conclusion I have to say that this task is far away from perfect but not bad for a start.
Regards
Fridtjof
Datafile SSIS Transform XML - Help
May 19, 2007Hi,
I want to insert the contents of a data file into a SQL Server table with XML datatype column using SSIS. I am fairly new to SSIS and script tasks.
Example:
Salary Data file:
EmpID, Name, Salary
100, John, 100000
200, Scott, 200000
Database Table:
create table xmlTable (id int identity(1,1), xmlColumn XML)
The contents of the file needs to be written to xmlTable. For each row in the Salary Data file there will be a corresponding row in the xmlTable.
xmlTable (Result):
1, <EmpID 100...>
2,<EmpID 200...>
Any help with details is greatly appreciated.
Thanks,
Rob
Multi-Merge Transform
Jun 14, 2006I would like a component that has the following functionality (if this already exists, let me know):
Pretty much, I want a multiple input merge join transform that joins X number of inputs so long as they have the same sorted key value. I only care about inner joins.
The reason for this component is that I have a data flow that is having to perform a LOT of lookups. Having to do them one after another is incredibly slow, so I thought that I could multi-cast them and perform several at once, which works fine, but merging the army of split inputs is messy with multiple sorts and merge join transforms. I can live with it this way, but having a single transform to collect all the inputs would look a lot nicer and a lot less to configure.
Cheers!
Brandon
Transform Active X Script To What?
Jan 23, 2007HI
I'm totally new to integration services, I was savy with DTS but now having been told to get on with it in SQL 2005 I'm a bit stuck.
I've got an ActiveX task which basically does the following:-
Function Main()
DTSDestination("Title") = DTSSource("Title")
DTSDestination("Surname") = DTSSource("LastName")
DTSDestination("FirstName") = DTSSource("FirstName")
DTSDestination("OtherNames") = DTSSource("PersonCode")
IF (IsNull( DTSSource("LastName") ) ) and (IsNull( DTSSource("FirstName") ) ) THEN
IF Not (IsNull( DTSSource("PersonCode") ) ) then
DTSDestination("Surname") = DTSSource("PersonCode")
Else
DTSDestination("Surname") ="Satff Name not set"
END IF
END IF
DTSDestination("TelephoneNumber") = DTSSource("Telephone")
DTSDestination("CollectorNo") =DTSSource("ID")
IF DTSSource("Active") =1 then
DTSDestination("StatusRef") = 1
ELSE
DTSDestination("StatusRef") = 2
END IF
Main = DTSTransformStat_OK
End Function
Thus as you can see I want to query the value of a source field and then put what I need in the destination coloumn - how do I go about doing this in SQL 2005, I've had a look at trying to do this in a dataflow - script task but can't seem to get the destination columns to appear like the source columns do? any ideas
many thanks
scooby