SSIS Execution Monitoring with Failure-Alert Email. A lot of things can and do go wrong when using a suite of SSIS packages to populate a SQL Server data warehouse with files from various sources on a daily basis. When something does go wrong, it is important to detect and rectify it in a timely manner. A missing data file or a file containing incomplete, corrupt, or improperly formatted data can cause a package to fail and data to be omitted, while a file that doesn't get transferred in time can cause the previous day's data to be duplicated. While SQL Server and SSIS have native execution-logging capabilities, they are geared toward the needs of DBAs and do not capture such fundamental things as the timestamp of a processed data file or package-execution end-time, which can be extremely useful pieces of information for determining when an ETL anomaly has occurred.
SSIS Execution Log Table To capture and utilize SSIS execution information not collected by the built-in logging features we will create a log table for storing the: Conclusion. Parsing Parameters in a Stored Procedure. When you need to pass a parameter to a stored procedure with a variable number of values you don't have the option of using an array or pointer type mechanism, which is found in other programming languages. The solution many people use is to pass in a chain long enough to house a collection of values with each value separated by commas or another identifier.
Once inside the stored procedure, you often use a loop to split the string and create a temporary table containing the different values. This temporary table will be used later in the queries of the stored procedure. For those of us that come from procedural programming languages such as VB or C + +, the strategy of using the loop to split the string is natural: we look for the first delimiter's occurrence and, if found, we separate the string into two parts, the first being one of the parameter's values.
While being completely valid, I dislike this code: even without the validating section. Declare @sParameter = 'WA,AZ,WA,TS' Why? Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server « Journey to SQL Authority with Pinal Dave. This is a very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps. CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values. Create TestTable USE TestData GOCREATE TABLE CSVTest(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate SMALLDATETIME)GO Create CSV file in drive C: with name sweetest. text with the following content. 1,James,Smith,19750101 2,Meggie,Smith,19790122 3,Robert,Smith,20071101 4,Alex,Smith,20040202 Now run following script to load all the data from CSV to database table.
BULK INSERT CSVTestFROM 'c:\csvtest.txt'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')GO--Check the content of the table.SELECT *FROM CSVTest GO--Drop the table to clean up database.DROP TABLE CSVTest GO Reference : Pinal Dave ( Like this: Like Loading... Parsing an ADO result set into a comma separated string in SSIS. Background I recently had a need to build a package that would check for any changes in table definitions in a source system and send a notification email listing the changed tables.
One option was to use a script task but I wanted to see if there were other ways to do simple programming using simple SSIS control flow components. This article presents one way of parsing values in an ADO result set into a comma separated list. Building the SSIS Package Step 1: Retrieving your list of tables Let's just use this simple SQL so that we can populate our ADO result set with data: select 'My first Table' as table_nmunion select 'My second Table' as table_nmunionselect 'My Third Table' as table_nm Note that the ResultSet is set to "Full Result Set". Step 2: Looping Over the Result Set Once the list of tables is stored in our object variable, we can loop over it using the Foreach Loop container. Step 3: Building the comma separated list using Execute SQL Task Step 4: Generating your Email message.
String Functions. Aggregate Functions - SQLServerPedia. SQL Server Online Resources: CTP, Troubleshooting | Microsoft MS. SQL Server 2014 Download the free trial and see how in-memory processing built into SQL Server 2014 delivers breakthrough performance to accelerate your business and enable new, transformational scenarios that make you more competitive. Microsoft offers comprehensive in-memory technologies for OLTP, data warehousing, and analytics built directly into SQL Server, so there is no need to buy specialized hardware, expensive add-ons or learn new skills to speed your transactions, your queries and your insights.
Download Microsoft SQL Server 2014 Express Microsoft SQL Server 2014 Express is a free, feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs. Download now and receive resources from the experts. SQL Server 2014 Express Samples.