Showing posts with label button. Show all posts
Showing posts with label button. Show all posts

Friday, March 30, 2012

Running an SSIS Package after a webpage button click

Hi all!
I'm a newbie when it comes to SSIS and web development (I'm currently in school for Mechanical Engineering). I'm trying to develop a website in which I can upload an excel file and have the contents written to a SQL database. I have been successful in creating a package in which I point to an excel file and it writes the contents to the desired database. With this, I was hoping that I can modify my website to take the file which is being uploaded, change the name, and then have the SSIS package run, in which it will look for the named file in a certain directory and upload the contents to the SQL server.

Ideally this would be the order of events:

User selects and uploads a file to the server (button click)
Server renames it and places it in a certain directory
SSIS package runs and extracts the data to SQL database

Thanks all!
Shrey

Sorry, I just realized that I didn't explicitly ask my question:

Is it possible to run a SSIS package when someone clicks on a button on a webpage?

|||Sure:
http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx|||Can someone show me some visual web developer code for a simple site which will run a SSIS package when someone clicks on a button?

|||

skshah39 wrote:

Can someone show me some visual web developer code for a simple site which will run a SSIS package when someone clicks on a button?

Did you read through all of the link I posted earlier? There are some links inside that page that should point you to some examples.

Wednesday, March 28, 2012

Running a Stored Procedure without passing parameters

HI all,

I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.

thanks.


The code would be similar to what you have if you had parameters. Just chop off the part of the code where you add the parameter.|||

thanks for that, however I can't use the normal way I used with parameters :

SQLDataSource1.InsertParameters("Till_Name").DefaultValue = tillname

As for the above I've used the InsertParameters method of my SQLDataSource1 object, which requires a parameter, although all I want to do is trigger a Stored Procedure. Do you follow?

|||

Have a look at the link

http://msdn2.microsoft.com/en-us/library/w1kdt8w2.aspx

Quoting the relevant portion from the link

"

If the database you are working with supports stored procedures, you can set theSelectCommand property to the name of the stored procedure and theSelectCommandType propertyStoredProcedure to indicate that theSelectCommand property refers to a stored procedure. The following example demonstrates a simple stored procedure that you can create in SQL Server:

CREATE PROCEDURE sp_GetAllEmployees AS
SELECT * FROM Employees;
GO

To configure theSqlDataSource to use this stored procedure, set theSelectCommand text to "sp_GetAllEmployees" and theSelectCommandType property toStoredProcedure.

Most stored procedures use parameters. For more information about using stored procedures with parameters, seeUsing Parameters with the SqlDataSource Control.

At run time,SqlDataSource control submits the text in theSelectCommand property to the database, and the database returns the result of the query or stored procedure to theSqlDataSource control. Any Web controls that are bound to the data source control display the result set on your ASP.NET page.

"

|||

thanks for that ambarsihg, I have my stored procedure as a SelectCommand, however my problem is the syntax of running the SelectCommand from a button press for example.

e.g.

SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSqlDataSource2.SelectCommand()
This doesnt work, so how do I do it please?|||Dim myConnectionAsNew SqlConnection([your connection])
Dim myCommandAsNew SqlCommand("[your stored procedure]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.ExecuteNonQuery()

ExecuteNonQuery executes the Stored Procedure

|||Thanks very much, much head scratching avoided!Smile

Monday, March 26, 2012

Running a job from a Report using a button? (Is that possible)

Here is the deal. We have a mapping table that stores basic info (like
4 columns). These columns are used to populate parameter boxes in a
report (some parameters depend on the values of others and so we had
to use a mapping table for performance increase). The mapping table
is loaded hourly using a job that calls an SSIS package. Simple
stuff. However, let's assume the user logs in at 12:30 and there is
new data that is not showing, they cannot wait 30 minutes until the
job runs again. At the same time, we can't make the job run that
frequently. So we want to set up a "button" that the users can click
on and it will allow the job to run. We cannot have the job run every
time the report is loaded, takes too much time and would defeat the
purpose of performance enhancement.
So far I have this script in a dataset that points to the msdb
database on our reporting box:
exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
ID!!! I've pressed the ... button near the dataset to try to hard
code the parameter there, no luck.
Also, what would I use to add the user functionality, namely them
being able to click on something to invoke the job?
Thank you so much.I would like add buton to rerun an SSIS package. How did you add a button in
RS?
"Ayman" wrote:
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>|||Hi,
I think the problem is the "@.". Try to use a sproc instead of a text
command.
"Ayman" <aymantg@.gmail.com> schrieb im Newsbeitrag
news:1184855299.083046.294670@.z24g2000prh.googlegroups.com...
> Here is the deal. We have a mapping table that stores basic info (like
> 4 columns). These columns are used to populate parameter boxes in a
> report (some parameters depend on the values of others and so we had
> to use a mapping table for performance increase). The mapping table
> is loaded hourly using a job that calls an SSIS package. Simple
> stuff. However, let's assume the user logs in at 12:30 and there is
> new data that is not showing, they cannot wait 30 minutes until the
> job runs again. At the same time, we can't make the job run that
> frequently. So we want to set up a "button" that the users can click
> on and it will allow the job to run. We cannot have the job run every
> time the report is loaded, takes too much time and would defeat the
> purpose of performance enhancement.
> So far I have this script in a dataset that points to the msdb
> database on our reporting box:
> exec dbo.sp_start_job @.job_id=N'd6ca06e2-73c4-4fe0-8924-b24bdc8cc78e''
> I put it as a text command and it works fine, BUT ASKS ME FOR THE JOB
> ID!!! I've pressed the ... button near the dataset to try to hard
> code the parameter there, no luck.
> Also, what would I use to add the user functionality, namely them
> being able to click on something to invoke the job?
> Thank you so much.
>