SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTSX Pkg From Stored Procedure (completely frustrated)


DTSX Pkg From Stored Procedure (completely frustrated)

Author
Message
Garth Zaleschuk
Garth Zaleschuk
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 70

Help please!! I am bruised and bloodied from mashing my head, for two weeks now, against stuff I do not know.

Ultimately I am trying to get an Excel (.xls) directly into a SQL Server (2016) table.

Here is what I have done so far using Goolge for assistance.

1)

EXEC sp_addlinkedserver
@server = 'ExcelServer2',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'P:\Test\RPT-AR3-007.xls',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
--------
2)

SELECT * INTO Table_1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=P:\Test\RPT-AR3-007.xls', [Sheet1$]);
GO
--------
I get:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
------
3)

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
------

4)

Argh! Expletive!!


OK, that does not work… so I’ll use DTS (2016) to convert my Excel file to a CSV. I know how to get a CSV directly into a table within SQL Server.

DTS pkg creation works and creates a proper CSV file. I save the DTS to a pkg within the File System. DTSX created… don’t ask about saving it to a SQL IIS cause no one at Corporate can help me with that one either.

5)

Reach out

So my question is : How do I execute the Pkg from within a Stored Procedure (preferred) or from the Agent. I would help if the explanation is laid out so a two year old can understand (KISS principle method).


Sreekanth B
Sreekanth B
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4472 Visits: 2993
You could just use Import Export wizard from SSMS(SQL Server Management Studio) to dump data from Excel file to SQL Server table. I would stay away from creating Linked servers for this task. Refer to this article...This might help.
https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 264
1. Must enable 'show advanced options'
2. Make sure 'Microsoft.ACE.OLEDB.12.0' is ready in Provider of link server. Otherwise, download and install it. Note, I guess you need x64 version - AccessDatabaseEngine_X64.exe

Then, OPENROWSET should do its work.

GASQL.com - Focus on Database and Cloud
Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7083 Visits: 1342
If P:\ is a mapped drive then it won't be available to SQL Server. You will need the UNC path.
curtw
curtw
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 19
These are all great recommendations. Excel is tough, users can type whatever they want and this can be difficult to process.

If this is a one-time process, and if the spreadsheet is small, consider creating the table in your database, then pasting values into the table. Sometimes for smaller one-time jobs, I'll even generate INSERT statements on the spreadsheet, then paste to SSMS to analyze and execute. This gives you code that is easy to debug if needed, such as when fields have bad characters, or when specific rows should not be inserted.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3436 Visits: 531
Garth Zaleschuk - Monday, February 26, 2018 12:55 PM

Help please!! I am bruised and bloodied from mashing my head, for two weeks now, against stuff I do not know.

Ultimately I am trying to get an Excel (.xls) directly into a SQL Server (2016) table.

Here is what I have done so far using Goolge for assistance.

1)

EXEC sp_addlinkedserver
@server = 'ExcelServer2',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'P:\Test\RPT-AR3-007.xls',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
--------
2)

SELECT * INTO Table_1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=P:\Test\RPT-AR3-007.xls', [Sheet1$]);
GO
--------
I get:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
------
3)

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
------

4)

Argh! Expletive!!


OK, that does not work… so I’ll use DTS (2016) to convert my Excel file to a CSV. I know how to get a CSV directly into a table within SQL Server.

DTS pkg creation works and creates a proper CSV file. I save the DTS to a pkg within the File System. DTSX created… don’t ask about saving it to a SQL IIS cause no one at Corporate can help me with that one either.

5)

Reach out

So my question is : How do I execute the Pkg from within a Stored Procedure (preferred) or from the Agent. I would help if the explanation is laid out so a two year old can understand (KISS principle method).


Kindly explain whether this activity is an ad hoc one ? OR you want to create a Stored Proc and schedule it thru SQL Server Agent job ?

Garth Zaleschuk
Garth Zaleschuk
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 70
Thanks for some direction. Additional data... This will be an ongoing process and I'll be required to with several Excel files, some will required weekly processing, others daily and at certain point of the month hourly. Some of these Excel files have more than 65k rows and it is in .xls format so I need to deal with multiple sheets within the excel file.

My issue in not so much creating a DTS Package as opposed to running it from a Stored Procedure. The data I receive is not clean and I must ETL it before finial processing (I got that covered). Having the import and the ETL within the same Stored proc is important as the turnaround time is important (source to final). Or within the same scheduled agent job... but again I am unfamiliar with how to schedule a DTS pkg from the agent...
Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7083 Visits: 1342
Hi Garth,
You said "but again I am unfamiliar with how to schedule a DTS pkg from the agent"
When you have the package open in SSDT under Project on the ribbon, select Deploy and select the SQL Server. This will save the package in SSISDB. Create a new job in SSMS on the SQL Server you deployed the package to and create a new job step, name it and select SQL Server Integration Services Package on the type drop down, Enter the Server, navigate to the path and select the package you deployed. Add a schedule and viola, you have it scheduled.

Garth Zaleschuk
Garth Zaleschuk
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 70
K, I had a look at SSDT and had a stroke... So in place of that I recreated my DTS package and saved it within Sql Server. It takes an Excel file from another server and dumps it into a SQL Database. When I run the package from Integration Services it executes with success... I can run it multiple of times with success.

However, once I place it into a Job within the agent and execute the job it fails every time.

I appreciate the assistance.
sgmunson
sgmunson
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89728 Visits: 6919
Garth Zaleschuk - Tuesday, February 27, 2018 1:11 PM
K, I had a look at SSDT and had a stroke... So in place of that I recreated my DTS package and saved it within Sql Server. It takes an Excel file from another server and dumps it into a SQL Database. When I run the package from Integration Services it executes with success... I can run it multiple of times with success.

However, once I place it into a Job within the agent and execute the job it fails every time.

I appreciate the assistance.

The service account that SSIS runs under has to have access to the file location that the package is deployed to, or if within SSISDB, then the job has to be changed to say that the source of the package is SQL Server as opposed to saying it's in the file system. Said "service account" also would need access to the network file share that holds the spreadsheet, regardless of how the package is deployed.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search