Using Stored Procedures In SSIS
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
ADVERTISEMENT
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 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
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
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
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
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
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 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
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 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
Apr 25, 2007
Hi Everyone
Please pardon my inexperience, I am new to SSIS. I am having some difficulty with using Oracle
procedures in SSIS. I have installed and configured the Oracle Client Software for 10g. I have managed to
create a connection to the Oracle database that I will be using. I am currently using the Oracle Provider For
OLE DB. I want to add an OLE DB Source component to the Data Flow which I then want to configure to
use an Oracle procedure to bring back the data that I need.
When I want to execute the Oracle Proc in the same way that I would normally execute a SQL Proc it returns
an error saying:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
I have tried using a ADO.NET connection and then using a Data Reader Source component but I get an
error with my SQL Statement saying "Invalid SQL Command"
Could anyone please provide me with some information on what I am doing wrong? Or possibly point me to
information that will help me? I have been searching the net non-stop without success.
View 15 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jun 17, 2006
hi
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
looking forward for a replygayathri
View 1 Replies
View Related
Dec 15, 2006
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
View 1 Replies
View Related
Feb 24, 2007
Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.
View 4 Replies
View Related
Feb 25, 2007
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource?
And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"?
Now I have around 40 stored procedures, and quite many of them looks the same except the where-part...
(Iam just a beginner with SQL)
View 2 Replies
View Related
May 2, 2007
Hello every one,
I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you
Thank you
View 5 Replies
View Related
Jun 19, 2007
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
View 1 Replies
View Related
Jul 5, 2007
Is there a website or somewhere i can go to read up on stored procedures??
View 2 Replies
View Related
Aug 2, 2007
cREATE PROCEDURE emp @val varchar(50)AS
declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null'
execute(@test)
GO
this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
View 2 Replies
View Related
Nov 18, 2007
Hi,
Can anyone help me. I've created a stored procedure in sql server and I'm trying to run it from my asp.net page. On Database Explorer I can't see it, or any for that matter, however I can see tables in the same schema.
Also I can't see it when I build a table adapter either.
Can anybody help?
Thanks
Sam
View 5 Replies
View Related
