Μπορείς να δημιουργήσεις ένα SSIS package το οποιό διαβάζει από ένα excel source και κάνει εισαγωγή τα δεδομένα από τά φύλλα του σέ αντίστοιχους πίνακες μίας βάσης.Κατόπιν να το αποθηκεύσεις στο δίσκο σέ όποια θέση επιλέξεις .
Μπορείς μέσα στην βάση του SQL να δημιουργήσεις ένα πίνακα όπου θα έχει δομή όπως παρακάτω
create table ExcelFilesSource
(
id int identity(1,1),
excelPath varchar(255)
)
Κατόπιν μπορείς να δημιουργήσεις μία stored procedure στον SQL όπου μέσα από ένα κερσορα του παραπάνω πίνακα θα μπορεί να εκτελέσεις το utility dtexec με την χρήση της xp_cmdshell όπως αυτό περιγράφεται παρακάτω στό help από τα books online .
Πηγή Βοηθείας : (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm )
ΚΑΛΗ ΤΥΧΗ
The dtexec command prompt utility is used to configure and execute SQL Server 2005 Integration Services (SSIS) packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.
The utility has four phases that it proceeds through as it executes. The phases are as follows:
- Command sourcing phase: The command prompt reads the list of options and arguments that have been specified. All subsequent phases are skipped if a /? or /HELP option is encountered.
- Package load phase: The package specified by the /SQL, /FILE, or /DTS option is loaded.
- Configuration phase: Options are processed in this order:
- Options that set package flags, variables, and properties.
- Options that verify the package version and build.
- Options that configure the run-time behavior of the utility, such as reporting.
- Validation and execution phase: The package is run, or validated without running if the /VALIDATE option was specified.
Exit codes returned from dtexec utility
When a package runs, dtexec can return an exit code. The exit code is used to populate the ERRORLEVEL variable, the value of which can then be tested in conditional statements or branching logic within a batch file. The following table lists the values that the dtexec utility can set when exiting.
| Value |
Description |
|
0 |
The package executed successfully. |
|
1 |
The package failed. |
|
3 |
The package was canceled by the user. |
|
4 |
The utility was unable to locate the requested package. The package could not be found. |
|
5 |
The utility was unable to load the requested package. The package could not be loaded. |
|
6 |
The utility encountered an internal error of syntactic or semantic errors in the command line. |
Utility syntax rules
All options must start with a slash (/) or a minus sign (-). The options that are shown here start with a slash (/), but the minus sign (-) can be substituted.
An argument must be enclosed in quotation marks if it contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space.
Doubled quotation marks within quoted strings represent escaped single quotation marks.
Options and arguments are not case-sensitive, except for passwords.
Using dtexec from the xp_cmdshell
You can run dtexec from the xp_cmdshell prompt. The following example shows how to run a package called UpsertData.dtsx and ignore the return code:
|
![]() Copy Code |
EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"' |
The following example shows how to run the same package and capture the return code:
|
![]() Copy Code |
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"' |
![]() Important: |
| In Microsoft SQL Server 2005, the xp_cmdshell option is disabled by default on new installations. The option can be enabled by using the Surface Area Configuration tool or by running the sp_configure system stored procedure. For more information, see xp_cmdshell Option.
|
![]()
Syntax
|
dtexec /option [value] [/option [value]]... |
![]()
Parameters
Remarks
![]()
Examples
Running Packages
To execute an SSIS package saved to SQL Server using Windows authentication, use the following code:
|
![]() Copy Code |
dtexec /sq pkgOne /ser productionServer |
To execute an SSIS package saved to the File System folder in the SSIS Package Store, use the following code:
|
![]() Copy Code |
dtexec /dts "\File System\MyPackage" |
To validate a package that uses Windows Authentication and is saved in SQL Server without executing the package, use the following code:
|
![]() Copy Code |
dtexec /sq pkgOne /ser productionServer /va |
To execute an SSIS package that is saved in the file system, use the following code:
|
![]() Copy Code |
dtexec /f "c:\pkgOne.dtsx" |
To execute an SSIS package that is saved in the file system, and specify logging options, use the following code:
|
![]() Copy Code |
dtexec /f "c:\pkgOne.dtsx" /l "DTS.LogProviderTextFile;c:\log.txt" |
To execute a package that uses Windows Authentication and is saved to the default local instance of SQL Server, and verify the version before it is executed, use the following code:
|
![]() Copy Code |
dtexec /sq pkgOne /verifyv {c200e360-38c5-11c5-11ce-ae62-08002b2b79ef} |
To execute an SSIS package that is saved in the file system and configured externally, use the following code:
|
![]() Copy Code |
dtexec /f "c:\pkgOne.dtsx" /conf "c:\pkgOneConfig.cfg" |
![]() Note: |
| The package_path or filespec arguments of the /SQL, /DTS, or /FILE options must be enclosed in quotation marks if the path or file name contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space.
|
Logging Option
If there are three log entry types of A, B, and C, the following ConsoleLog option without a parameter displays all three log types with all fields:
|
![]() Copy Code |
/CONSOLELOG |
The following option displays all log types, but with the Name and Message columns only:
|
![]() Copy Code |
/CONSOLELOG NM |
The following option displays all columns, but only for log entry type A:
|
![]() Copy Code |
/CONSOLELOG I;LogEntryTypeA |
The following option displays only log entry type A, with Name and Message columns:
|
![]() Copy Code |
/CONSOLELOG NM;I;LogEntryTypeA |
The following option displays log entries for log entry types A and B:
|
![]() Copy Code |
/CONSOLELOG I;LogEntryTypeA;LogEntryTypeB |
You can achieve the same results by using multiple ConsoleLog options:
|
![]() Copy Code |
/CONSOLELOG I;LogEntryTypeA /CONSOLELOG I;LogEntryTypeB |
If the ConsoleLog option is used without parameters, all fields are displayed. The inclusion of a list_options parameter causes the following to displays only log entry type A, with all fields:
|
![]() Copy Code |
/CONSOLELOG NM;I;LogEntryTypeA /CONSOLELOG |
The following displays all log entries except log entry type A: that is, it displays log entry types B and C:
|
![]() Copy Code |
/CONSOLELOG E;LogEntryTypeA |
The following example achieves the same results by using multiple ConsoleLog options and a single exclusion:
|
![]() Copy Code |
/CONSOLELOG E;LogEntryTypeA /CONSOLELOG
/CONSOLELOG E;LogEntryTypeA /CONSOLELOG E;LogEntryTypeA
/CONSOLELOG E;LogEntryTypeA;LogEntryTypeA |
The following example displays no log messages, because when a log file type is found in both the included and excluded lists, it will be excluded.
|
![]() Copy Code |
/CONSOLELOG E;LogEntryTypeA /CONSOLELOG I;LogEntryTypeA |
SET Option
The following shows how to use the /SET option, which lets you change the value of any package property or variable when you start the package from the command line.
|
![]() Copy Code |
/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue |
Ιωάννης Μανουσάκης