I have to Run 3 SQL querys on the remote server and log the no of rows affected on my local server.
i.e I need to insert all the 3 values in to my local table.
Please help me out how to do it on SSIS.
Thanks in advance.
View Complete Post
We are using Execute Process Task in SSIS package to execute a batch file to house keep the generated flat files which are older than a week from the archive folder.
The same batch file is used to delete the some temp files generated in the process.
As the Archive folder and temp folder paths are configurable, we are trying to pass the folder paths as parameters to batch file.
I used Expressions tab of Execute process Task Editor to map the variable to Arguments property. This is working fine. but I need to pass one more variable as an Argument.
Can some one help me how to achieve this. Thanks in advance.
I'm sure there is a simple solution to this, however to save my banging my head against a brick wall: -
Within the "Execute SQL Task Editor" I have the following; -
Parameter Mapping Tab; -
Variable Name; - System::PackageName,
Directtion; - Input,
Data Type; - NVARCHAR,
Parameter Name; - 0,
Parameter Size; - 100
General Tab; -
SQLStatement; - INSERT INTO dbo.CustomSSISLog (System::PackageName) VALUES (?)
When I click on the "Parse Query" button I get the following error; - "
The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.
The table exists as follows; -
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[CustomSSISLog](
[CustomSSISLogKey] [int] IDENTITY(1,1) NOT NULL,
[PackageName] [nvarchar](100) NULL
) ON [PRIMARY]
Any help would be greatly appreciated.
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGINÃÂ PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGINÃÂ PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.ExecutingÃÂ 'execute BEGINÃÂ PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
Please help me.. thanks in advance
I am a great fan of using Package configuration in SSIS to ease migration. I am stumped when it comes to sql execute task. We have few execute sql task that requires selecting/updating across mutliple databases in the query. Currently, I am using 4 part
name(server.database.schema.table) in query. Doesn't this actualy against theme of using package configuration in SSIS. If I hard code server and database name, I will have to edit each SSIS package post migration to production.
What options do I have? Can you please help this rookie?
Which is a better way to do in Execute SQL Task : Direct SQL input or Create a stored proc in database and then use that.
In my opinion, Stored Procedure is a better and recommended way for following reasons:
Please let me know if this is not the case with SSIS Execute SQL Task.
There is a SSIS package containing (among other executables) a data flow task which writes a string to a DataReader Destination. I need to execute just that data flow task and read the string from DataReader Destination programmatically.
I can run the whole package from C# and then read the value. Also the data flow task in question must be embedded in the package.
Does anyone know how to do this?