Integration Services :: Execute Stored Procedures In Parallel And Dynamically?
Feb 19, 2015
I have a stored procedure. It can be executed like this
exec test @p = 1;
exec test @p = 2
exec test @p = n;
n can be hundred.
I want the sp being executed in parallel, not sequence. It means the 3 examples above can be run at the same time.
If I know the number in advance, say 3, I can create 3 different Execution SQL Tasks. They can be run in parallel.
However, the n is not static. It is coming from a table.
How can I execute Stored Procedures in PARALLEL and DYNAMICALLY ?
I think about using script task. In the script, I get the value of n, and the list of p, from the table, then running a loop with. In the loop, I create a threat and in the threat, I execute the sp like : exec test @p = p. So the exec test may be run parallel. But I am not sure if it works.
I'm pretty new to SSIS but I've managed to cobble together a number of individual packages to refresh SQL tables from a 3rd-party database.
Now, what I'd like to do is have a single package that I can use to invoke each of the individual ones. Since it will run on a quad, I'd like to invoke them such they'll run in parallel.
I have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!
DECLARE @sql VARCHAR(MAX) SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'') print @sql EXEC sp_executesql @sql
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
I have a set of stored procedures that return a fixed list of columns from a fixed list of parameters.
Is it possible to use 1 Report file to display these reports, and just switch the stored procedure name at runtime within .NET code, or will this reset the parameter list and/or column list?
Every day an application creates new tables and dumps static info into them.
I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.
1) Get a list of un-archived tables. 2) Foreach table do the following.
a. Export the table into raw file. b. Zip the raw file. c. Update archive tracking table.
As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata. How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:
The column "some_column" needs to be added to the external metadata column collection.
The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.
Then I get this error: Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50) declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
declare @ODSDB varchar(50) declare @SQLSTMT varchar(max) set @ODSDB = 'SampleDB' begin set @SQLSTMT = ' IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.
I'm writing a stored procedure, in which I dynamically create an SQL statement. At the end of this, the SQL statement reads like:
Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.
If I hard code a statement, like
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger that works fine (although it brings back a count of all the lines).
But if I modify the dynamically created SQL Statement from earlier on to:
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0 it doesnt work - it complains: Must declare the scalar variable "@LineCount".
Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)
So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?
I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
I am using the below code to send HTML Email body to multiple recipients and CC, its working fine. Now i have to attach multiple files in that mail. Is there any possibilities to attach multiple files with the below code else provide any other code to achieve this task.
Code: /* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script.
I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.
Also in package i used ADO.net as source for *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
I am trying to generate a report using SSRS ( SQL server 2012 & Visual studio 2013). when I try to execute stored procedures I am getting below error. Below are the details of enviroment.
1. SQl serverr 2012 2.Oracle 12c client 3.Oracle Server 12c 4.Visual studio 2013
we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.
A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.
The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.
In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?
I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors. sp_ActiveDirectory_Obj Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171 sp_ActiveDirectory_SCP Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171 sp_ActiveDirectory_Start Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19 Invalid object name 'sys.sp_add_agent_parameter' Line 60 Invalid object name 'sys.sp_add_agent_profile' Line 123 Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17 sp_addalias Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44 Incorrect syntax near '%'. Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64 'get_sid' is not a recognized built-in function name. Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119 Incorrect syntax near '%'. sp_bindefault Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273 Incorrect syntax near '%'. sp_databases Invalid object name 'sys.sp_databases'. Line 6 sp_tables Invalid object name 'sys.sp_tables'
Is there a chance that these errors are due to the fact that I executed them without parameters?
This is for SSIS 2008r2. I am generating flat files successfully with a datetime stamp (filename_yyyymmddhhmm). Now I need to append a MAX(FILEDATE) from the file. I have a query to do this, but am not sure about two things:
1) Is it advised to put the query in a Script Task (with db conn and so forth) or is it better to put it in an Execute SQL Task? I am thinking the latter but am not 100% sure.
2) How would I pass the result of this query (yyyymm) to the filename. The filename format would be (filename_yyyymm). I am assuming that I would probably need to pass the result into a variable/expression but am not quite sure of the steps involved.
I have an SSIS package which calls a command line app.When run in BIDS, it executes normally. The command line app is passed the arguments, does what it needs to do.When called as a SQL Agent Job (by the agent, or by me) it fails when calling the app, giving an exit code of 2 (which is an exception trapped by a try-catch). The SQL Agent service is running under my user (it's a test environment). The argument passed (from the log) is valid, and I've run it against the app, it provides the appropriate output.I can't for the life of me figure out what's going wrong.The app is passed an argument of a path and a password, and applies the password to the file, using interop.
Hi I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2. For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure. In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user. Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this. If anyone can point me in the right direction on this... I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005? Thanking you in advance
