Microsoft SQL Server Integration Services: Foreach folder enumerator Case I want a Foreach Folder Enumerator, but the Foreach Loop component only loops through files. Solution Unfortunately this isn't supported by the standard Foreach Loop component, but there are a couple of workarounds: - Use an open source component Directory Foreach Enumerator or my own Custom Foreach Folder Enumerator - Use a Script task to accomplish this. This example uses the Script task and a Foreach Loop to enumerate through the folders. The Script tasks generates a list of Directories and the Foreach Loop loops through that list. 1) Control Flow Drag a Script task and a Foreach Loop container to the Control Flow like the image below. 2) Variables Create three string variables: startlocation that indicates the parent folder wherein we gonna search for subfolders. 3) Script Task Select the startlocation as a readonly variable (we only read the path inside it) and select xmldoc as a readwritevariable (an xml string will be stored in it).
The Ultimate Visual Studio Tips and Tricks Blog - Site Home - MSDN Blogs This article came about as the result of a recent trip I made to a customer. I was presenting on TFS and made the, oft repeated, statement that Agile is better than Waterfall. Now I have to admit that I have never really had anyone challenge me on this assumption since most of the people I know just accept this as truth. On this particular day there were a couple of project managers in the audience and they were none too pleased about my assertion. For the rest of the hour, we went back and forth on the issue. Exchange 1 Project managers: “You can’t say that agile is better than waterfall, it simply isn’t true.” Me: “I have twenty years of evidence backing up my claim and I have personally seen it work this way.” Exchange 2 Project managers: “Well, we have government regulations we deal with and you just don’t understand what we do here.” Me: “I have [another customer] that has to deal with HIPPA requirements and they use Agile so I don’t think your requirements are that strict, are they?”
Andy Leonard : SSIS Expression Language and the Derived Column Transformation Introduction This post is part of a series titled An Introduction to the SSIS Expression Language. In this post, I demonstrate the SSIS Expression Language and the Derived Column Transformation. To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at Add To The Demo Project If you have not already done so, create an SSIS project named ExpressionLanguageDemo. Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to DerivedColumn.dtsx. Drag a Data Flow Task onto the Control Flow. Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox: Select ContactID ,Title ,FirstName ,MiddleName ,LastName ,EmailAddressFrom Person.Contact Click OK to close the editor. Learning To Derive Let's start somewhat in the middle. The Replacement
Editing Published SSIS Package in SQL Server 2012 | Ms SQL Girl So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. SQL Server 2012 Integration Services now is now making use of Project Deployment Model. A. B. Caveat: Both these options require access to the project on the server. Option A: New Project 1. 2. 3. 4. 5. 6. This option is one of the safest / simplistic ways of getting SSIS project from the server. Option B: The shortcut 1. 2. 3. 4. 5. 6. Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Special thanks to Josh Fennessy (b | t), for providing Option B. Would it work for SSDT Visual Studio 2012? Wrap Up Further Reading
Import XML into SQL Server using SSIS - Jon Tavernier - Blog This example was written using SQL Server 2008 R2. In SQL Server 2005, Microsoft introduced the XML column data type. I find it much easier working with XML directly in SQL Server over using the built-in XML Source component in SSIS. This example shows loading two XML from the file system into a SQL Server table using SSIS. Package Setup First, setup a Foreach Loop Container (FELC) to loop over the directly in question and assign the file name to a variable. Within the FELC, add a Data Flow Task (DFT). Extracting the XML file contents from the file system (Script Component).Converting those contents to Unicode (Data Conversion).Storing those contents into a SQL server table (OLE DB Destination). Script Component The script component is setup to take two input Read Only Variables: the directory and the current file name being processed as passed from the FELC. Data Conversion Storing in SQL Server The two XML files loaded were rather small: Here they are loaded in SQL Server: Questions? Share
How to make an HTTP request from SSIS sql - Is it wise to disable a Task by using an expression evaluation