Edit: This is not new content. I've reposted this with corrected image links. In my previous blog post, I showed a row-by-row solution to creating an excel workbook dynamically using SSIS and populating one worksheet per row of data. Then "studio72" asked if this method could be adapted if the test data "users" had more than one row of data in the resultset as follows: Name | Age | Job John 25 IT John 34 ENG Anna 21 HR Anna 24 IT Mary 20 MFG I want the SSIS to create one excel file with sheets f...
Read More
Edit: This is not new content. It is posted with corrected image links. vree asked a question in the forums regarding manipulating a set of encrypted files in SSIS. So I put together the following tutorial to show one way one might accomplish this task. First, I created 2 folder on my D:\ drive named, "PGPIn" and "PGPOut". These will be my source and destination folders for the ( simulated ) PGP decryption that will occur. I created 2 PGP files within the PGPIn folder - this was to simulate 2 fi...
Edit: This is not new content. I am reposting with corrected image links. "Studio72" asked a question in the SSIS forum that peaked my curiosity. In essence: Could a fixed data format with a variable number of rows be exported to Excel with one worksheet created per row of source data? Honestly when I read the question, I thought, "No, SSIS doesn't work that way.." But I asked a few questions and started tinkering - following is my solution to the "problem" / question. Package Variables - Let's ...
bsingha asked a question in the forums regarding how to update a single cell in one Excel sheet using the data from another sheet. So, I decided to post a quick blog post on how to accomplish this. First - make sure that SQL is installed and service packed up to date. Make sure Excel is installed on the machine which will be used ( in my case, I used Excel 2007 because I already had it installed ). Excel ( or office ) will also need to be service packed up to date. Once that's all done do the fo...
"jampa.sb" asked a question about moving files around in SSIS using the File System Task and variables in the SSIS forum. Based on that question, I decided to put together a "Step-By-Step" walk through demonstrating how to do a portion of the issue. From the question, there are 3 folders and to start, 2 files: Folder A\ Folder B\ Folder C\ In "Folder A", a file named "a20120111.txt" gets created. In "Folder B", a file named "b20120111 .txt" gets created. The question ( goal ) is to rename a COPY...
keerti posted a question on my wall asking if I could provide any links to aide in the understanding of using LINQ from an SSIS package. As it happens, I'm actually not familiar with much about LINQ - but I'll take any opportunity to learn something new - even at a higher level. So with a bit of searching ( I searched "+LINQ +SSIS" at google ) - the following is what I was able to find: First thing, it looks like the package must be set up to use .Net 3.5 per: http://social.msdn.microsoft.com/Fo...
A little about me: I’m a bit of “gadget geek” – I’ve been working with and on computers since 1982 when I got my first Atari 1200xl. I was hooked almost immediately. I spent hours typing in code listings and hours more waiting for them to save ( and load ) from cassette tapes. Now days ( 2011 ), I’m a windows developer turned database administrator. I’ve got an MSDN subscription as well as my still very intact “love of computers.” I have a pi...
Just a quick note that SQL 2012 - Release Candidate 0 is up on Microsoft's site now. http://www.microsoft.com/sqlserver/en/us/future-editions.aspx From what I see so far - it's a free ( with registration ) download and installs / runs as a 180-day evaluation. There are 32 and 64 bit versions available separately ( ISOs ) or you can get a DVD ISO image with both versions.
The Question I misread a question about a table that contains "Parent -> Child" data in the SSRS forums here on BIDN recently and I decided to attempt answering it. I thought it was a question that many of us have no doubt run across before - "how do you show a hierarchy for data when you have a table with "parent" and "child" information listed in the row values? So I thought I'd give it a shot and share my results. Let's get into it Like my math professor always said, "first, make sure you ...
This is an answer to a forum post, but since it was a bit lengthy, I thought it best to make a Blog post. The question The original question is here if you'd like to read up on it: http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/1204/-pdf-source/1#4657 Pre-requisites For this one are going to include "GhostScript 9.02" - which is an opensource package for working with PDF files. In this case, I'm going to use it to create a .JPG of the first page of the PDF file f...
If you did not already know - I'm a fan of the BIDN forums. Especially the "SQL Server" ( more general ) and the "Integration Services" forums. I'm also a fan of the Chinese proverb , "Give a man to fish and he'll at for a day. Teach a man to fish and he'll eat for a lifetime." I try to share the knowledge I have and often joke that, "..I can only keep track of about 2 things at a time, but I'm always happy to share both things I know." What I'm finding in sharing my knowledge however is that ...
"Using the resources of BIDN.com - a quick HOWTO guide" I've assembled this over a few weeks to "give back" to the site. If you know or regularly meet users and want to encourage them to join the site, this little guide has some of the ( very ) basics to get them started. It's broken down into relatively short HOWTO documents with lots of screenshots and step-by-step instructions for basic bidn.com usage. It's free - copy it, burn it, upload it, do whatever you need if you think it will help som...
"I need you to add a header with the client number and a summed total amount as well as the row count at the top of the file you send to us daily. I'll send you the file format that all of our clients use." The task seems simple enough. Let's see, SSIS can SUM up data. Check. SSIS can COUNT using the same component. Check. Flat-file destination data adapter. Check. "Sure thing, boss I can put that together in a few hours for you!" Have you been there? I have on more than one occasion. Mainframes...
I'll just say it - I'm a Google junkie I'll also admit that I like using this site -- except when it comes to using the search feature. Here's a quick hint to let you use Google to search this ( or any Google searchable ) site. Let's say I way to see what all I've done on BIDN. I'd jump over to http://www.google.com with my browser and enter my search terms - in this case, my first and last name. Then at the end of the search, I would add on "+site:bidn.com" as seen below. Note: I didn't get 1.7...
I really have not posted much in the way of blogs on here lately. I've been putting together what I'm hoping will be a useful ( and free ) .PDF that you ( as a BIDN user ) will be able to pass on to people you meet that helps them get set up and familiar with BIDN. It's a series of HOWTO -style documents written in an easy to follow and very informal fashion. Right now, I'm working on a section dealing with: HOWTO use the BIDN forums So I thought I'd share some thoughts from the "using the forum...
About 9 months ago I posted about an experience I had with Pragmatic Works support. ( http://www.bidn.com/blogs/KeithHyer/bidn-blog/1031/pragmatic-works-why-i-told-my-boss-now-that-is-customer-service ). I'm still with the same company ( NOT Pragmatic Works ) and once again, I was not asked to write this up. I'm also not being compensated in any way for doing so. A few days ago I had to call Pragmatic Works support again. Let me preface this by saying, I read the blogs of several of their employ...
Prerequisites: Let's assume you have installed SQL Server 2005 client tools and the Visual Source Safe client on the same machine. You're all updated, patched and ready to start some query writing. Naturally ( it's natural - right? ) you want to have your code in source control. "Even your queries related to the project?", you may ask. Yes - even those. If you open up SQL Management Studio and you don't see the source safe integration there, here is what you do. Steps: First, go into SQL Server ...
I'm no SSIS internals expert - so PLEASE correct me if I'm wrong here. I've been reading up and learning about component development in SSIS and want to take a crack at explaining why your metadata gets out of sync in SSIS ( my experience is with 2005 - if 2008 is different, please post it below ). It's all about input and output IDs internally. So let's start with a simple data flow that takes a source, sorts it and writes it to a destination. Behind the scenes, SSIS is keeping track of some st...
Problem: I want to take a filename that I have in one variable and add the date and time to it. Mini Specification: For the example process, I'm assuming that I have a filename "VendorOutput.txt" in a variable. As this is created, I'm going to be archiving this each day - and possibly multiple time per day, so I'm going to name in the format "VendorOutput_{YYYY}{MM}{DD}_{HH}{QQ}.txt" ( where QQ is minute since MM was already used for month ). As for adding seconds or milliseconds - well I'll als...
Let me start by saying: No, I do not work for Pragmatic Works ( http://www.pragmaticworks.com/ ). However, I know a lot of the contributors and supports here do. I'm not a well-known "SQL Guy" ( or chicken ) I'm just your every day DBA - I like to get things done and I like challenges / learning. I also try to share what I learn. In my experience with DBAs this seems "normal". Also please note that my sharing this experience was not requested and I'm not being compensated or anything. I just thi...
Problem: Everyone that's programmed a system that users will interact with has run across a statement like this one in the fact-finding stage. "I want to be able to search on phone number. I can't imaging needing to search on anything else.. Well, ok maybe last name. First name would be useful too. What I really mean is - whatever I pass it, it should just find that combination.. You know?" For this example, I'm going to do an "exact" ( based on my collation ) match. I'm not going to be doing a ...
Lately, I've been enjoying various shows on the Science channel about Time and Space. No one yet understands exactly what time is yet - still, everyone wants to save some. I'd like to show how one might save a little time in the long run using " Template Parameters " ( see http://msdn.microsoft.com/en-us/library/ms189396(v=SQL.90).aspx ). Template parameters are just a simple way of doing a find and replace before a query is run. Problem: Consider an example like this one - you have 2 SQL server...
What's the issue? Have you ever worked on a table with an identity column and then deleted data from the table? It creates "gaps" in the ID numbers. Today I found myself looking for missing IDs so I would know where my gaps were. So what do you do? First, let's recreate the conditions in a temp table: SET NOCOUNT ON go IF EXISTS( SELECT TOP 1 1 DoesItExist FROM tempdb..sysobjects WHERE id = object_id( 'tempdb..#mytestrows' ) ) BEGIN DROP TABLE #mytestrows END go CREATE TABLE #mytestrows( ID INT ...
Introduction: Mike Davis, a frequent contributor and insightful writer, published an article today demonstrating a method to convert cursor code into an SSIS package loop. http://www.bidn.com/articles/ssis-development/125/replacing-a-cursor-with-ssis Following is a different ( not necessarily better - just different ) approach to tackling the same issue - without using looping. This post will not answer the question originally posed in that article ( as it will not include a SSIS-specific soluti...
Intro: Free software - it's always right in everyone's price range and when one gets more out of it than one paid for it - there tends to be contentment. Recently when tasked with transferring some files via sFTP, the author found ( ok - Google found ) WinSCP ( link in Resources section below ) which is an open source application for sFTP. For the following information, one will need to have it installed. A guide exists on the website detailing using the application from within SSIS. The followi...