DTSX Pkg From Stored Procedure (completely frustrated)
-
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).
-
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/ -
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.exeThen, OPENROWSET should do its work.
GASQL.com - Focus on Database and Cloud
-
If P:\ is a mapped drive then it won't be available to SQL Server. You will need the UNC path.
-
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.
- Garth Zaleschuk - Monday, February 26, 2018 12:55 PM
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 ?
-
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...
- 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. -
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.
- Garth Zaleschuk - Tuesday, February 27, 2018 1:11 PM
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Cool, thanks... I'll verify or make adjustments as per ur suggestions.
-
K, thanks for the guidance but still no joy. I can create a DTS package that works, I can save it tot he File System or to SQL Server. I can run the package from SSIS Stored Packages either as File or MSDB and all works. I can not get them to run in the agent... 100% failure. I have looked at and set up a proxy and scheduled for a future time... 100% failure
- sgmunson - Tuesday, February 27, 2018 1:21 PM
Do you know what account the SQL Agent runs under? That account MUST HAVE permissions to the file location for the source spreadsheet, as well as access to the database in which the destination tables exist, and any necessary permissions that may be associated with updating or inserting (as appropriate) those tables. You didn't mention any of this in your last two posts, nor did you quote which post(s?) you were responding to, so I'm not even sure you are responding to my post above, which I "quoted" here just for reference.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I am thinking it is all related and you (the forum) is helping me chip away at my issue, so I am thinking all one thread it is just that the issue is evolving as I get closer to a solution.
Do you know what account the SQL Agent runs under? Hmmm, I set up a 'Credential' called SSIS Execution Account with the Identity My User Name. My user name has almost full privileges to the box and I use that to create everything on the box and within SQL Server. I also created or modified the Proxy SSIS Proxy with the Credential name SSIS Execution Account and SQL Server Integration Services Package checked off.
When adding the Job Step I use SSIS Proxy in the 'Run As' dialogue
So I am assuming that because everything points back to my user name there should not be any permission issues although I have been horribly wrong in the past 🙂
- Garth Zaleschuk - Wednesday, February 28, 2018 11:31 AM
I am thinking it is all related and you (the forum) is helping me chip away at my issue, so I am thinking all one thread it is just that the issue is evolving as I get closer to a solution.
Do you know what account the SQL Agent runs under? Hmmm, I set up a 'Credential' called SSIS Execution Account with the Identity My User Name. My user name has almost full privileges to the box and I use that to create everything on the box and within SQL Server. I also created or modified the Proxy SSIS Proxy with the Credential name SSIS Execution Account and SQL Server Integration Services Package checked off.
When adding the Job Step I use SSIS Proxy in the 'Run As' dialogue
So I am assuming that because everything points back to my user name there should not be any permission issues although I have been horribly wrong in the past 🙂
I wouldn't do it that way. I'd be sure that the SQL Agent Service Account is a domain account that has the necessary permissions, both for the file location AND for the database and table(s?) being accessed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply