SSIS packages (1 Viewer)

Mittle

Member
Local time
Today, 10:37
Joined
Dec 2, 2020
Messages
105
am trying to design SSIS for ETL from access/excel sheets to SQL Server and will require transformation with script task in VB.


the SQL Database in question is on a Shared server and cant get SA permission on the virtual box.


the suggestion from the 3rd line support team is to create and design my package o my laptop with windows 10 and install Visual studio/ SSDT

ive always designed and tested SSIS packages on a server .


Is it possible to design, run and test SSIS on my laptop with Visual studio installed . My laptop has windows 10 . anyone done this before please?
 

Minty

AWF VIP
Local time
Today, 10:37
Joined
Jul 26, 2013
Messages
10,353
Yes - you can create, debug and run them exactly as if you where on the server.

If you want to publish them to an Azure service to be run automatically it all gets a bit more involved, however, with normal SQL server it should be virtually identical to the task on the server.

You obviously will still need the correct permissions to connect.
 

Mittle

Member
Local time
Today, 10:37
Joined
Dec 2, 2020
Messages
105
thank you but when you say correct permission to connect . I have db_owner permission on the database .
in the past ive always developed on the Server with SSIS installed and BIDS/SSDT with SA rights on my server .

OK I will try it out . just wanted to know if anyone done this before
 

Isaac

Lifelong Learner
Local time
Today, 03:37
Joined
Mar 14, 2017
Messages
8,738
I've only developed ON the server a handful of times. And normally develop them on my local with VS and SSDT.
I would say sure this is absolutely the norm, since in many shops the ssis devs aren't allowed near the production server anyway.

You should consider skipping the vb.net script task with Excel automation, in favor of simply, one of the following:

1) loading the raw data into a staging table then using SQL as necessary
2) using an ssis transformation task if possible
 

Mittle

Member
Local time
Today, 10:37
Joined
Dec 2, 2020
Messages
105
ssis transformation task?am confused

do you mean SSIS transformation task can convert that VB code I included in my previous post ?

still need to use a programming language wont we. so what will be the difference between script task and SSIS transformation task


Thanks
 

Isaac

Lifelong Learner
Local time
Today, 03:37
Joined
Mar 14, 2017
Messages
8,738
ssis transformation task?am confused

do you mean SSIS transformation task can convert that VB code I included in my previous post ?

still need to use a programming language wont we. so what will be the difference between script task and SSIS transformation task


Thanks
I'm suggesting that, possibly, you consider actually use a Transform type of SSIS task to transform the data rather than Excel.

To me if someone tells me, "Your job is to take this bunch of Excel data, along with a bunch of VBA code that manipulates it, and translate the whole thing into a SQL server-driven project", to me that would mean, "use a combination of SSIS and T-SQL to ingest the data & manipulate it as needed", rather than, try to use SSIS to continue perpetuating the VBA code itself into the new project.

It could be a transformation task isn't enough for whatever the vba was doing, but you could still ingest the data and use t-sql to manipulate it rather than any vb*
 

Users who are viewing this thread

Top Bottom