Performance Comparision - Stored Procedures VS SQL (Inside SSIS)
Mar 23, 2007
I am working on a technical design of data integration ETL package which will be moving data from SQL Server Source to DB2 destination. I currently have two options, when moving data to DB2(IBM AS400). I can call a AS400 Stored Procedure, and pass in the data to the stored procedure, and perform the insert processing within the AS400 environment or I could do inserts from SSIS in a DFT and write individually to AS400 tables. My question is from a performance and good practice perspective, which method should I move forward with. I need a possible list of pros- and cons when using AS400 Sproc vs using SQL within SSIS? I would really appreciate response from individuals who have done something similar in the past. Thanks a lot and I am really looking forward to responses.
View 1 Replies
ADVERTISEMENT
Apr 18, 2007
Hi everybody,I am trying to alter a view inside a Stored Procedure and sql server is not allowing me to do so.Can we not have an alter view inside a Stored Procedure?Here is the code: CREATE PROCEDURE dbo.[UPDATE_VIEW_LINEITEMALLOTMENT] -- Add the parameters for the stored procedure here@MAINID int,@BE VARCHAR(8)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. --SET NOCOUNT ON; ALTER VIEW [Budget_Management_System].[dbo].[LineItem_OCAFund] AS SELECT TOP (100) PERCENT SUM(dbo.ALL_OCAFUND.AMOUNT) AS Expr2, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC, dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_LINEITEM.MAINID FROM dbo.APP_LINEITEM INNER JOIN dbo.ALL_ORG ON dbo.APP_LINEITEM.ROWID = dbo.ALL_ORG.LINEITEMID INNER JOIN dbo.ALL_OCAFUND ON dbo.ALL_ORG.ROWID = dbo.ALL_OCAFUND.ORGID INNER JOIN dbo.APP_AMENDMENT ON dbo.ALL_ORG.AMENDMENTID = dbo.APP_AMENDMENT.ROWID AND dbo.ALL_OCAFUND.AMENDMENTID = dbo.APP_AMENDMENT.ROWID GROUP BY dbo.APP_LINEITEM.LINEORDER, dbo.APP_LINEITEM.BE, dbo.APP_LINEITEM.MAINID, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC, dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_AMENDMENT.AMENDMENTORDER HAVING (dbo.APP_LINEITEM.BE = @BE) AND (dbo.APP_LINEITEM.MAINID = @MAINID) AND (dbo.APP_AMENDMENT.AMENDMENTORDER = 1) ORDER BY dbo.APP_LINEITEM.LINEORDER ENDthanks a lot,Murthy here
View 7 Replies
View Related
Aug 13, 2001
I have created a cursor using the following type of syntax:
DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor
Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
View 2 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
Jan 19, 2000
How could I tell the performance difference between two queries:
One is:
select * from table
where Lower(colomnname) = 'value'
The other is:
select * from table
where colomnname = 'value'
Basically the difference is in lower() function, how much this function will affect the query performance.
Is there a formal way to test it out, or by any logic.
Thanks, Mike
View 2 Replies
View Related
Apr 2, 2006
Do you know some performance differences between reading data from a stored procedure using:
1. OLE DB Connection and OLE DB Source
2. ADO.NET Connection and DatareaderSource
Przemo
View 1 Replies
View Related
Oct 30, 2005
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
View 7 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
May 27, 2004
Hi, im developing an action site and im having proplems with stored procedures
that are causing extreme load on the server.
PLEASE LOOK AT THE CODE AND TELL ME IF U SEE ANYTHING THAT CAUSES LOW PERFORMANCE OR SOME SUGGESTIONS ON HOW TO INCREASE THE PERFORMANCE.
SHOULD I F.E. TAKE THIS LOGIC FROM THE DB AND TRY DOING IT IN c#?
The action-site uses an auto-bid-feature that is causing a load.
Basically when a user makes a bid, i have to loop through all other users that have a bid in
the product and automatically increase a bid untill it matches the highest bid of a user.
F.e. a user bids auto-bid $2000 in a product where another user has a $2100 bid in the product and the current bid is $1200. So the SP loops through the users increases the bid-history untill it mathces the highest bid of a user.
- $1250
- $1300
- $1350
- etc...
This is the SP that loops through the users and calls another function each time to increase the bid in the bid-history.
------------------------------------------
CREATE PROC HaekkaBodIUppbodi
@uID INT
AS
DECLARE @curUpphaed DECIMAL
SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID
IF(@curUpphaed IS null)
BEGIN
SET @curUpphaed = 0
END
DECLARE @curCount INT
SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
WHILE(@curCount>0)
BEGIN
-- skilgreinum notanda breytu
DECLARE @notandi NVARCHAR(255)
IF(@curCount<2)
BEGIN
SELECT @notandi = kaupandi_netfang FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
EXEC HaekkaBodAVoru @uID, @notandi
BREAK;
END
-- búum til cursor fyrir alla notendur
-- sem eru með hærri boð en núverandi boð
DECLARE crs CURSOR FAST_FORWARD FOR
SELECT kaupandi_netfang FROM bod WHERE upphaed > @curUpphaed
AND uppbod_ID = @uID
ORDER BY dags
-- opnum cursorinn
OPEN crs
-- sækjum fyrsta notandann inn í @notandi
FETCH NEXT FROM crs
INTO @notandi
DECLARE @returnValue INT
SET @returnValue = 0
DECLARE @tempValue INT
SET @tempValue = 0
-- lúppum í gegnum notendurna
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC HaekkaBodAVoru @uID, @notandi
FETCH NEXT FROM crs
INTO @notandi
END
CLOSE crs
DEALLOCATE crs
-- sækjum hæstu upphæð í boðsögu
SELECT @curUpphaed = MAX(upphaed) FROM bodsaga WHERE uppbod_ID = @uID
SELECT @curCount = COUNT(kaupandi_netfang) FROM bod WHERE upphaed >
@curUpphaed AND uppbod_ID = @uID
END
GO
-------------------------------------------------------------
And here is the SP that increases the bid...
CREATE PROC HaekkaBodAVoru
@uID INT,
@notandi NVARCHAR(255)
AS
-- Hækkun á uppboði
DECLARE @haekkun DECIMAL
SELECT @haekkun = haekkun_upphaed
FROM uppbod WHERE id = @uID
-- Hæsta boð í vöru
DECLARE @haesta_bod DECIMAL
SELECT @haesta_bod = MAX(upphaed) FROM bodsaga
WHERE uppbod_id = @uID
-- Hæsta boð sem notandi vill gera
DECLARE @haesta_bod_notanda DECIMAL
SELECT @haesta_bod_notanda = upphaed FROM
BOD WHERE kaupandi_netfang = @notandi
-- Seinasti notandi sem gerði boð í uppboðinu
DECLARE @seinasti_notandi NVARCHAR(255)
SELECT @seinasti_notandi = notandi FROM
BODSAGA WHERE uppbod_id = @uID
DECLARE @upphaed DECIMAL
SET @upphaed = @haekkun
-- Ef búið að bjóða í vöruna
IF(@haesta_bod IS NOT null)
BEGIN
IF(@haesta_bod = @haesta_bod_notanda)
BEGIN
SET @upphaed = @haesta_bod
END
ELSE
BEGIN
SET @upphaed = @haesta_bod + @haekkun
END
END
-- Ef hæsta boð notanda er sama eða lægra en núverandi verð
-- og notandi var ekki sá síðasta til þess að setja inn boð
IF(@upphaed <= @haesta_bod_notanda)
BEGIN
-- Ef notandi hefur EKKI sett inn þessa upphæð áður...
IF((SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @upphaed)=0
-- Og notandi á EKKI núverandi hæsta boð
AND (SELECT COUNT(*) FROM bodsaga WHERE notandi = @notandi AND upphaed = @haesta_bod)=0)
BEGIN
INSERT INTO bodsaga
VALUES(@uID, @notandi, @upphaed, GETDATE())
RETURN 1
END
END
RETURN 0
GO
View 2 Replies
View Related
Jul 31, 1998
I`ve written a pretty complex powerbuilder application and my performance
is fair use stored procedures. Is there any way to force sql server 6.5
to reevaluate the current query plan and if it is bad create a new
query plan.
View 2 Replies
View Related
Jul 20, 2005
Hello folks,I usually use this way to store more procedures inside a single SP:CREATE PROCEDURE usp_MyProc(@usp_mode int)ASIF @usp_mode = 1BEGINENDIF @usp_mode = 2BEGINENDand so on...My question is about performance: I don't know deeply how SP executionruns, is there a performance fall following this way? Do you haveother methods or suggestion instead of this way to avoid having dozensof procedures inside my database (obviously I use this method becauseI have many procedures and I prefer a more compact view andorganization)?TIA, tK
View 3 Replies
View Related
Nov 8, 2000
Hello Everybody,
I posted this same question couple of times in the news groups but no answers. I have a 2 tables and i am doing a union query using a view. each has 250 rows. The query takes 20 seconds to return the results. no joins or anything. the create view simply looks like this:
create view myview as
select id, name from table1
union
select id,name from table2
Where as if i write a stored procedure like below, it returns the rows in 4 seconds.
create table #mytable
( id int, name varchar(30))
insert into #mytable (id, name) select id, name from table1
insert into #mytable (id, name) select id, name from table2
select id,name from #mytable.
I prefer doing in the view since both returns the same result. I tried running dbcc, update statistics. but no luck. Can anyone please help me in this issue.
Thanks
Ramesh
View 3 Replies
View Related
Jul 23, 2005
What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
May 25, 2007
Hi,
First Question is can we use Stored Procesdures in SSIS..if yes where i mean by useing which transformation how..?
Secondly i am stuck up here..:
SELECT JAM_ID = a.JAMGCD,
JAM_NM = a.JVDQVN,
JAM_DESC = CONVERT(varchar(50),SUBSTRING(a.JAMATX,1,CHARINDEX('..',a.JAMATX )-1)),
JAM_CODE = ?
from table1...
I am using this Query in the OlEDB Data flow task to retrieve the columns which i need and do some transformations..on them...now i get JAM_CODE by using JAM_DESC can i use it...i think i cant
....can anyone suggest how to deal with this.?
thanks
ravi
Nothing much that i can do..!!
View 2 Replies
View Related
Aug 22, 2007
Once again I have a configuration database (CD) question. I am trying to use SP_HELPSRVROLEMEMBER and SP_CONFIGURE in the CD. I am having difficulty calling the stored procedures in the data flow task. What I would like to do, is to just call both SP's in the OLE DB Source and insert them into the OLE DB Destination. However, this doesn't seem to be that easy. Does anybody have any ideas as to how to insert the results from a SP into a central server? Any ideas would be great.
-Kyle
View 5 Replies
View Related
Aug 4, 2015
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW.  PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
View 3 Replies
View Related
Jul 23, 2015
How to use Stored Procedures in SSIS?
View 2 Replies
View Related
Jul 23, 2007
All,
Here is my problem, its very simple, But I dont have a solution.
To run / import / what ever I else I forgot (?) SSIS in SQL SERVER, what are permissions I'll need.
So far I have developed everything in BIDS, when I try to migrate it to a sql server by using Import package in Integration Services I got the below error.
TITLE: Import Package
------------------------------
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)
The error is very clear in itself, While I have raised a request for the execute permission of this stored procedure, i also like to know what kind of permissions I will need in MSDB to work with out any problems. So that I dont have to go to DBA for execute permission for each error I may get for this.Right now I dont have execute permission on any of the Stored Procs in MSDB.
If any body can show any pointers that would be help full.
View 4 Replies
View Related
Jan 29, 2008
Hi,
I have a small requirement because of which i am facing some migration problems.
For changing the variables say InitialCatalog and Custom logging destination path, i am making use of XML configuration files. Till here i have no issues.
But problem starts here. Basically DTS package is called by calling a stored procedure from a front end application.
Within the stored procedure the package location say (D:Packages<packagename.dtsx>) is being hard coded due to which i need to change the package
location paths each time when i am migrating the stored procedures to Testing and Production environments.
Is there any efficient way of avoiding the hard coding of the package paths using Windows Environment vars or something else.
If so please help me out.
Any help would be greatly appreciated.
create procedure spexecDTS_Pkg
@g_p1 varchar(50),
@g_p2 varchar(50)
AS
declare @jid uniqueidentifier
declare @cmd varchar(4000)
SET @cmd = '"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /F "D:Packages est.dtsx" '
SET @cmd = @cmd + ' /SET "Package.Variables[User::userid].Properties[Value]";'+'"'+@g_p1+'"'
SET @cmd = @cmd + ' /SET "Package.Variables[User::cname].Properties[Value]";'+'"'+@g_p2+'"'
DECLARE @jname varchar(128)
SET @jname = cast(newid() as char(36))
-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'Samples',
@delete_level = 1,
@job_id = @jid OUTPUT
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid
Thanks in advance.
View 5 Replies
View Related
Sep 26, 2007
Hi, i have a text file destination which i am trying to output data to via an SSIS package. I cannot seem to be able to set up the package so that i execute a parameterised stored procedure and insert the data into a text file. I have set up the following
connections:
a. ADO.net connection to the SQL 2005 database
b. Flat file destination, which links to an empty file on the network : D:GCDReportsfeedsgmr_p201.fs_20070724
ANy quick help would be greatly appreciated.
David
View 3 Replies
View Related
Jul 31, 2007
I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.
I have performed the process manually running the stored procedures, providing the values directly and everything works fine.
I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.
One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.
This has become rather frustrating and would appreciate any assistance.
Thanks.
View 1 Replies
View Related
Sep 6, 2007
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
View 1 Replies
View Related
May 26, 2008
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
View 4 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Dec 15, 1999
One particular SQL stored procedure executes 25 times more slowly when invoked by an SQlAgent job than when executed directly. Any suggestions?
View 4 Replies
View Related
Mar 12, 2008
Hi all,
I use a "Exec DTS 2000" task.
I have a variable user::MYVAR in my SSIS package, wich value is "PARENT".
I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED".
After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT".
Is it possible to see the update from the parent package ?
Thanks
View 6 Replies
View Related
Sep 11, 2006
Hi everyone,
I've got a serious problem with SSIS packages. We've built an application -done with Framework 2.0- which throw SSIS packages on demand -according a criteria on NTFS-
Everything's fine but those packages which own ADO .Net connections. When our application meet some of them appears in our log:
I attached you the snippet of code:
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)
Till here, fine no throws any event.
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)
'here throws OnError event when meet the task issued
If sResultDts = DTSExecResult.Success Then
...
No se pudo adquirir la conexión "SRVDESASQL2005.msdb.usrSSIS". Puede ser que la conexión no esté configurada correctamente o que no tenga los permisos adecuados en esta conexión.
Tranlate into english, more or less:
It doesn't acquire the connection "<server.database.user>." It could be that the connection is not fine customized or maybe permissions are not suitable for this connection.
At the same time we've verified that all data sources are available. In a fact that same packages from the server is launched without incidences (manually)
Please let me know any comment or further information or any link related with this problem. With OLE Db connections we haven't problems at all.
Thanks in advance,
View 7 Replies
View Related
Sep 4, 2007
I'm reading over this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884062&SiteID=1
and I'm kinda lost as to what to do to strip out the dtd from an XML file I am downloading. I do NOT know XSLT and for that reason, I can't follow his logic.
My SSIS package downloads my XML file just fine, now I need to do a strip of the DTD line in my XML Task.
The person who provided the solution in the above post said to do this...
Code Snippet
Operation Type: XSLT
Source Type: Variable
Source: Variable's name containing the xml text
Save Operation Result: True
DestinationType: Variable
OverwriteDestination: True
Destination: Variable's name which is to contain the original xml minus the DTD.
SecondOperandType: Variable
That stuff I understood. I'll replace variables with my files because they are stored that way, but from what I can tell, that's not my problem.
The stuff he says below this comment is going over my head like a ton of bricks. I can't figure out how to do it.
This is the kind of line of my XML that I want to strip out.
https://www.myaddy.com/pbdr.dtd"[]>
and then he said this...
Code Snippet
Since XSL doesn't know about DTDs, telling it to copy everything strips out DTDs. Then use the Variable specified in the Xml task's SecondOperand as the Source data for the xml source.
Code Snippet
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<xsl:copy-of select="." />
</< FONT>xsl:template>
</< FONT>xsl:stylesheet>
A note on how to paste a multi-line xml document into a Integration Services String variable:
Integration Services String variables textboxes are not multi-line, in the Windows sense of a line (CR+LF),
So, in order to paste multi-line text (which xml docs almost always are), save a temporary copy with a unix line ending.
That is, create an xml file in visual studio, and paste your sample original xml in there. Go to File/Advanced Save options, and save the xml with the the settings of Encoding: Unicode (utf-8 without signature) - CodePage 65001, and most importantly, set the Line endings dropdown to "Unix (LF)".
After selecting "OK", copy and paste the text from Visual Studio's xml file editor into the IS variable, and you'll note all the xml data appears.
So can anyone walk me through a dummies version of what he is suggesting to do?
Thanks,
Keith
View 1 Replies
View Related
Feb 20, 2008
I have a RPC which gives me multiple - single record rows like
Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column
Now I need to split the above record into 6 Fields and populate in my local DB
I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's
Any ideas / directions?
View 6 Replies
View Related
