Bytes and Bites of Internet
SSIS
Importing Fixed Width file into database using SSIS package
Oct 13th
Ok, few weeks back I posted how to create a fixed width file using SSIS for test purposes. Then after that I posted TSQL way to import it into database, and now today I am going to show how to import it using SSIS package… I have used same database (which I used to generate file) and same file (generated using SSIS package) for this demo because … well because I can 
.
First step is to fire up VS 2010 with SSDT and create new SSIS project. And then select data flow task and drag it to control flow tab. Open up data flow task and select “flat file source” as source of data and drag it to data flow task work area. This will open up connection wizard which you will use to configure flat file source.
For those who don’t know what flat files are … they are just a txt or csv files. They could also be fixed position files. All of them uses some kind of row and column delimiter to separate data within them
Exporting data from SQL Server to Fixed Position Flat file
Aug 27th
Just as usual, this post is kind of “Bi-product” of my attempt to create a demo for another post in which I need to use fixed position file. At first I tried to create a fixed position file using BCP. BCP has query out option to export query results into a file, but I was unable to produce the fileformat which uses fixed position. I actually even tried to use format file to generate required format but I ran into many issues so finally I thought that it is more important to get results than means to achieve it (at least it is true in this case 
) and I fired up Visual Studio to use SSDT.
Actually it is very easy to generate a fixed position file using SSIS. All you have to create is a simple Data Flow task and then add a SQL Server as source and a flat file as destination into that task. Only thing to be sure is configuring Flat File destination settings.
Logging in SSIS Package
Jan 19th
Logging is an essential part of any application. It gives user an insight of application operation. And it proves valuable in event of any issue. Same stands true for SSIS packages as well. Since SSIS package will get executed usually by SQL Server Agent or some Windows Scheduled Task and in both case it is usually unattended, having logger gets really helpful when all of a sudden you start receiving alerts that some files are not being processed.
For this post, I have create a very simple demo to show how logging works in SSIS. For this demo, I am using file system task to transfer file from one folder to another folder. And I have created a log file to record each step of this process.
First I have added a file system task. And then when you right click on control flow tab, it gives few options and first option is logging. By selecting this option, it will open up another window which is basically logging configuration wizard. Select objects of containers for which you want to create logging. In this case I wanted to log everything for whole package so I selected folder. If you click on “Provider Type” you should see different logging options available to log events in SSIS. And good thing is we can select multiple logging options, for example file based logging for simple operational steps and windows event logging for critical errors. For sake of simplicity (and I am feeling lazy to go into too deep) I have selected “SSIS Log provider for text files”. There is another tab next to “Providers and Logs”, “Details” which lets user select which events user want to log into logger. If you click on “Advanced” it will expand current window and will give more fine grain choices for logging.
Using configuration file for SSIS package
Dec 23rd
SQL Server Integration Services (SSIS) are really nice ELT tool for data transformation, conversion and transfer. But they not of much use if they can’t be configured to accept different parameters. For example, a once task to move file is designed if it can’t be used to look for file in different folder or FTP task can’t connect FTP site other than the one coded in task itself. In these cases a configuration file generated to be used with DTSX package can be really handy. Configuration files for SSIS package allows us to make our package more flexible (or say configurable). We can store all kind of parameters into this configuration file, which will allow use to change package behavior by changing package parameters.
For sake example, I have created a sample project with file transfer task. This package just looks into specified folder and moves any txt file exist in that folder to another location. This project is using a configuration file which allows user to change target and source folder location, and we can also change what type of file this package will move (e.g. Text, PDF or all files).
Steps to create configuration file:
1) Create three variables using variables window. One will be source folder path, second will be destination folder path and third one is just a file name. (if Variable window is not open, it can be accessed from View –> Other Windows –> Variables)
2) Drop “Foreach Loop Container” into control flow tab. And then place “File System Task” inside of “Foreach Loop Container”. This basically means that this “Foreach Loop Container” will execute that “File System Task” every time it loops through.
“Copy database” operation, quick ‘n easy way to make duplicate database
Nov 13th
I was trying to create a demo for one of my experiment, and I had to make create duplicate database for that purpose because I didn’t wanted to do tests on my TestDB 
so quickest option was to use “Copy Database” feature in SSMS, because other option like restoring backup of DB with another name is kind of too much of work since this DB is just for testing I don’t have any backups of it.
So I choose this COPY DATABASE option, which is available in all SQL Server editions (I guess except Express edition). This is actually very easy step by step process.
Copy Database option can be accessed by right click on database that you want to copy then Tasks –>Copy Database.
More >