Seeking comments regarding this setup - SQL Server Express, SSDT, etc (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
I'm going to be setting up a process on a virtual private server running Windows 2019. MS Access, SQL Server Express will be involved.
I'll also be using the free version of SQL Server Data Tools in order to create an SSIS package to load some flat files into SQL Server.
(Actually, I'm still assessing my options there - I might just use T-SQL bulk load operations instead ... but for the sake of this question, I'm evaluating the viability of going the SSIS route for load).

First, my background includes a lot of experience as a developer, but NOT as a dba, although I have limited exposure to some dba activities.

I know that using the above-referenced tools, I'll be able to create a DTSX file and run the package in Visual Studio. However, I'm wanting to know how it can be more automated. If I am using SQL Server Express, I won't be able to have Integration Services or SQL Agent running.......Right? So is there still any way for me to fire off my SSIS package in an automated way? Or is the only solution for someone to open it and hit F5 every time? If that's correct, then this will probably lead to me deciding to use bulk insert (or similar IO) instead.

In fact the more I type this, the more I realize I might as well just use bulk insert rather than utilizing SSIS at all. But I'd like to confirm that I'm correct in stating, using those tools, I'd be able to create an SSIS package....but probably not automate the running of it, at least using anything normal.

Just wanting to triple-check if it sounds like I have said anything incorrectly or have forgotten any possibility or other thoughts, thank you for looking.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:47
Joined
Jul 9, 2003
Messages
16,244
I noticed you haven't had a reply yet, so I thought I would bump it up the list for another look... Not sure you'll get a bite just yet, it's the weekend, (UK Holiday) and I noticed many of our most knowledgeable and brightest members Appear to be Missing.
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
Thanks, @Uncle Gizmo
I was just thinking about this today with a clear head and couldn't remember WHY I wasn't considering the simple option of doing a standard data import (docmd.transferspreadsheet, or whatever) via Access into my linked ODBC sql tables. I think there was some reason originally, but I can't remember it any more! So I might be OK. So far the set up has worked well. Maybe just typing out the question helped me. o_O
 

Isaac

Lifelong Learner
Local time
Today, 09:47
Joined
Mar 14, 2017
Messages
8,738
Just remembered - because one of the source files has >255 columns. Will use sql bulk insert for that one.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:47
Joined
Jul 9, 2003
Messages
16,244
Maybe just typing out the question helped me.

That is often the most important step! Having to explain yourself, as if to someone else, can often lead you to see the error/solution yourself.
 

Users who are viewing this thread

Top Bottom