Write data from Multiple tables to single flat file

It's simple if you are transferring your single table data to a single file means its one-to-one mapping. Many threads I observed at MSDN forum where user want different set of data (Header, Detail, Trailer) in a single file or data from the multiple tables to single flat file. It's not difficult in SSIS but it is tricky.

Solution:

USE 3 dataflow tasks one for each table with precedence constraints to have them execute in the correct order.

Important: 

Each destination would use its own connection manager but they would all point to the same flat file. The only thing you would need to do is in the flat file destination property grid (or advanced editor) make sure you set the overwrite flag to false for the second and third dataflows so that the file will be appended to. 

DFT 1:
OLEDB Src (header table) --> FlatFile Dest

(using flat file conn mgr 1 (filename = OutputFile.csv), overwrite set to true)

DFT 2:
OLEDB Src (details table) --> FlatFile Dest

(Using flat file conn mgr 2 (filename = OutputFile.csv), overwrite set to false)

DFT 3:
OLEDB Src (trailer table) --> FlatFile Dest

(Using flat file conn mgr 3 (filename = OutputFile.csv), overwrite set to false)

----------------------------------------------------------------------------------------------------
Get the sample package..

Package.dtsx

For step-by-step explanation, please check the following document.

PPT:   OneOutputFile.ppsx

OR

Doc:  OneOutputFile.doc

----------------------------------------------------------------------------------------------------


There are lots of other ways to do this. but in the above method, you don't need any specific programming skill. Do let me know if you have any doubt..

Convert Number to IP address

Convert Number to IP address.

Use the following query to convert the number into an IP Address..


DECLARE @IP_NUM AS INTEGER
SET @IP_NUM=1135863234 

SELECT CAST(@IP_NUM/16777216 AS VARCHAR(3)) +'.'+  
CAST((@IP_NUM%16777216)/65536 AS VARCHAR(3)) +'.'+ 
CAST( ((@IP_NUM%16777216)%65536)/256 AS VARCHAR(3))   +'.'+ 
CAST(((@IP_NUM%16777216)%65536)%256 AS VARCHAR(3))

Result:
67.179.229.194

Please give some suggestion to make it better.

Load CUBE data to SQL table using Script task

Recently I faced an issue while working with SSAS 2008 cube. I was using a data flow task and inside that I have OLEDB source (The Analysis Services 9.0 OLE DB Provider (msolap90.dll)) for fetching data using MDX query. I was running this package in BIDS, its working fine. But as I was trying to run it with DTEXEC command, it’s throwing following error:


• ERROR message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008." Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".


Component "OLE DB Source" (2297) failed the pre-execute phase and returned error code 0xC0202009.

Environment:
SSIS package on SQL Server 2005. BIDS 2005
SSAS cube on SQL server 2008.


Solution:
I have tried many possible solutions but no success. Finally I wrote a script task code which is connecting to CUBE and dumping the cube data in flat file with comma delimited format. Later on I used this flat file to laod data in SQL table.

I would like to share this code, may this helps other.

Important: Please add the "Microsoft.AnalysisServices.AdomdClient.dll" dll to your reference.


Microsoft.AnalysisServices.AdomdClient.dll is available under the link below,http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
Download "Microsoft ADOMD.NET " from the link above and after running msi file you will get
Microsoft.AnalysisServices.AdomdClient.dll under folder.
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.

**********************************************************
Public Sub Main()
Dim serverName As String = "Manish"
Dim databaseName As String = "CRM"
Dim databaseID As String = databaseName
Dim cn As String = "Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName
Dim connex As New AdomdConnection(cn)

connex.Open()

Dim commandText As String

commandText = "SELECT [Country].[Country Code].MEMBERS ON 0, [Time].[Gregorian Calendar].[Year] ON 1 FROM [CUBE2008];"

Dim cmd As AdomdCommand = New AdomdCommand(commandText, connex)
Dim dr As AdomdDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

' output the rows in the DataReader
Using writer As StreamWriter = New StreamWriter("c:\myfile.txt")
While dr.Read()
For i As Integer = 0 To dr.FieldCount - 1
If (dr(i) Is Nothing) Then
writer.Write(" , ")
Continue For
Else
writer.Write(dr(i).ToString() + " , ")
End If
Next
writer.WriteLine()
End While
End Using
dr.Close()

Dts.TaskResult = Dts.Results.Success

End Sub

***************************************************************

Let me know your valuable comment to make this post better.

Processing Options for SSAS Objects

PROCESSING OPTION SSAS2008

Process Default: (Applicable for **All objects)
Performs the minimum number of tasks required to fully initialize the object. The server converts this option to one of the other options based on the object state.

Process Full (Applicable for All objects)
Drops the object stores and rebuilds the object. Metadata changes, such as adding a new attribute to a dimension, require Process Full.

Process Update (Only for Dimensions) 
Applies member inserts, deletes, and updates without invalidating the affected cubes.

Process Add (Only for Dimension & Partition)
Adds only new data.

Process Data (Dimension, cube, measure group, partition)

Loads the object with data without building indexes and aggregations.

Process Index(Dimension, cube, measure group, partition)
Retains data and builds only indexes and aggregations.

Unprocess (All Objects) 
Deletes the object data or the data in the containing objects.

Process Structure ( Only CUBE)
Deletes the partition data and applies Process Default to the cube dimensions.

------------------------------------------------------------

**SSAS objects list

• Database
• Dimension
• Cube
• Measure group
• Partition
• Mining structure
• Mining model

Script task recompilation Error

A package that has a script task runs fine in visual studio, but the package fails as a job with following error on DEV Server.

"Script could not be recompiled or run: Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

OR

“Error 1 Validation error. Script Task : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.”

Answer:

• Check the pre-complied property of script task.
• Change it to TRUE
• Add the following code in your script, before imports statement.

                    Option Strict Off

                    Option Explicit On
                    Imports System

                    .

                    . 

Load MS Access data to SQL server

Question: How to transfer MS Access data to Sql Server 2005?

Answer:
There are 2 ways for achieving this
1) SSMS: Import & Export Wizard
2) BIDS: Create a New SSIS package

First approach is easy and gives you flexibility to modify the SSIS package(If required)
I hope you can try this your self .

Let me know if you need my help for this.

Second approach: 
Open the BIDS and create a new package

Step1:
a) Create a new OLE DB Source connection.
b) Select the provider as "Native OLE DB\ Microsoft Jet 4.0 OLE DB Provider".
c) Select the Database file name. [Check the Image Below]

Step2:
a) Take a DFT.
b) In DFT drag "OLE DB Source", "OLE DB Destination" [or take any destination as per your requirement]
[Check the Image below]

Step3:
Before executing the package please makes sure that destination table should have the correct data type. [if not use data conversion task]


I hope this will help you. If you need more assistance or clarification, please leave the comment.

Delete bunch of dynamic files

Question: How to delete all files from a directory?


Answer: Please follow the following steps:

Step1: Add a For each loop container. & Inside it add a File system task.

Step2: Add 2 variables (Scope: Package level)

1. Var_FilePath
2. Var_InputFolder
   
   

   

Step3; Double click on Foreach loop container and in expression map the "Directory" property to Var-InputFolder. (Click on the image for larger view)

Please select the "Reterive file name" property to fully qualified.

Step4; Map the Var_FilePath in variable mapping tab.

Step5: Double click on File system task and change the operation to "Delete file" and Set IsSourcePathVariable property to TRUE. Then map your Var_FilePath to it. (Click Image)

Step6: Now select the File System Task and press F4 for property window. Set the "Delay validation" to True.

Step7: Execute your package.

Hope this will help you.

:)

Allocating a 'Set ID' & Extracting data in groups

Question:
Allocate a Set ID for a Batch of data and make a group.
OR
Retain Values from Previous Rows.

Following is the input data and desired output for that

Answer:

Drag a DFT in the control flow and add the following components.

For achieving this you need to use Script Component in DFT. and the following below steps: Before that you need to select Header as input column.

Create an output column in script component

Add the following code in the Script component.
'*******************************************************************************
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim int_Sno as integer ' // this variable should be global for the script (class level)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.RecType = "A" Then
int_Sno = int_Sno + 1
End If
Row.Sno = int_Sno

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
******************************************************************

Now execute your package and you will get the desired output (Given below)



Hope this will help you.

Excel destination - cannot convert between unicode and non-unicode

While using the Excel Destination we used to get following error:
Error1 Validation error. Data Flow Task: Excel Destination [1311]: Column "Order Date" cannot convert between Unicode and non-Unicode string data types.

OLE DB source query:
where order_date data type is datetime in DB. Here we are converting to varchar

DFT (OLE DB Source and Excel destination)

Table structure:

Answer:
I checked on MSDN for excel destination. Here is some content written about moving data for your reference: Data types. The Excel driver uses only six data types, which Integration Services maps as follows: · Numeric – double-precision float (DT_R8) · Currency – currency (DT_CY) · Boolean – Boolean (DT_BOOL) · Date/time – date (DT_DATE) · String – Unicode string, length 255 (DT_WSTR) · Memo – Unicode text stream (DT_NTEXT)

Integration Services does not implicitly convert data types. for resolving this problem please follow these steps.

Step1: Add Data Conversion task between Source and Excel destination

Step2: Edit the data conversion task and change the data type to [DT_WSTR], change the length if required.

Step3: After adding the data conversion, the error message will go. Execute your package.

It will create an Excel file and load the data.

How to include Primary ID column using SSIS package?

Question:
I have target table where columns are ID, Name & DateofBirth where ID is PK and should be increment of 1(eg. 1, 2, 3, 4... and so on) also NOTE that ID column is NOT AN IDENTITY column

Now I have flat file which has data with only Names & Dateofbirth.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/db36004b-30a8-4404-8f95-301cc4a628f1

Answer:
Hi, There are two ways.

First:: Download the "Row Number Transformation" and use it in your SSIS package.

The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value.

Second:: Add a "Script Component transformation" and write a script there for adding the row numbers. Please check the "Row Numbers in a DataFlow"for step-by-step explanation.