Call SSIS Pkg from Stored Procedure?

[jenlion]

I started this message on the general SQL programming forum and was referred here...

I am working with an application that allows me to run custom VB Scripts. I need a script to take an excel sheet, load it into a SQL table, and then run a stored procedure in the database.

However, I can't give the users running this application any special permissions to the database (like bulk insert and all that goes along with it). And I can't go row-by-agonizing-row because there may be a few hundred thousand items on the excel sheet.

It's been suggested that I write a vbscript that:
-- saves the given excel sheet to a predefined network share
-- calls a stored procedure that:
- Calls an SSIS package that imports the data from excel to my table
- then executes the transactions I need against the data in that table.

I don't want to hard-code a name/password into my script anywhere, but I'd be OK with doing it into an SSIS pkg. I'm not very familiar with SSIS though (I just know you could do that with DTS). Does this make sense? I have created an SSIS pkg that imports from fixed excel to a sql table... now I just need to know how to execute this package from a stored procedure when the stored procedure is called by a user that doesn't have bulk insert permission.

make sense? Doable? what do I need to do? Thanks...

 

[MDXer]

Do able although setting up a job the would run the SSIS package and having you app start that job would be much easier as you could configure the job to run as a specific user.

As for your package I remember that it was in a fashion that it would either update or insert records depending on is they existed or not? The reason I ask is there may be a faster way to do most if not all of this work in SSIS rather than SQL.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jenlion]

MDXer, I'd be fine with calling the SSIS pkg from the vbscript, sure. I have no idea how to do that, though, or if special permissions are required?

And yes, I am planning to "dump" the records into a table and then add or insert records to a regular table used by the application. The calling application will give me a WhseID; the excel sheet contains an ItemID; the database contains the ItemKey and WhseKey that I will have to look up. So, the excel sheet does not match the table in the DB exactly.

Thanks!

 

[MDXer]

The besuty of SSIS is that your source in no way has to resemble your destination. You transform your data as it flows through the pipeline adding removing data that is or isn't needed for your destination. Removing many of these functions from the SQL layer results in a huge boost in perfromance.

Example if you say you have an ItemID and WhseID need to result in the match of an appropriate Key. This is easily accomplished through the use of a Lookup task.

The lookup task executes a the query provided and joins it to the dataflow as you specify it to. For Example in very rough terms.

Lookup ItemID would have a query that may look like this

Select
ItemID
,ItemKey
From Items

You would set the relationship to be the itemid and insert the ItemKEY column. The Lookup task precaches it's dataset so all the needed data is there before the data from your excel file even begins to load. The lookup task then preforms an equi-join to match the records. This would preform the same as

Update MyStage Set
ItemKey = items.ItemKey
From MyStage stg
  JOIN Items on stg.ItemId = items.ItemID

I would suggest you look at everything that needs to happen before the data is inserted or updated and identify what can be done in SSIS and then build the package to do that.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[jenlion]

Paul,

I can do that -- but it still leaves me with my main problems:

How do I call the SSIS package from vbscript?

What permissions are required to call the package?

How do I store permissions within the SSIS pkg?

Thank you again...

 

[MDXer]

As i said in my first reply to the post set the package to run as part of a job that you can execute from your app. Your app starts the job the job runs the package you can have the job run the package under a predefined account that the application need not be aware of. This is then the only user that needs permissions. Most places have an account already established for the execution of jobs.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck