Top New Features in SSIS 2008

1) Create Script tasks by using Microsoft Visual C# and Microsoft Visual Basic .NET.

2) Use ADO.NET for tasks as well as for source and destination components.

3) Improve scalability with thread pooling and enhanced lookup transformations.

4) Perform more functional and scalable data transfers with the improved SQL Server Import and Export Wizard.

5) New SSIS connectors for SAP BW, Oracle and Teradata.

Dynamic query as sql command for ADO NET / OleDb Source

A question on MSDN forum drew my attention towards a very common requirement which is made very tricky to do in SSIS.

A user may need to pass a dynamic query to OleDb source/ADO source something like

SELECT * FROM TblNm WHERE Col1 = @Var

In OleDb source user can create query like this in a variable and pass it to the OleDb Source but ADO NET source does not have Variable as Data Access Mode, so what do we do for ADO Net Source. Okay dont scratch around there is way out which works for both OleDb and ADO Net Source.

1. Create a variable


2. Create ADO NET Source or OleDb Source and select SQL Command as DATA ACCESS MODE and give a simple query like SELECT * FROM TblNm


3. In Control Flow Select DataFlow Task which contains this Source and from it Property Tab select Expressions and open Property Expressions Editor


4. Choose [ADO NET Source].[SQL Commnad] and make a expression like

5. Now run the package and Sql will be dynamically created and will get the value of variable.

Fail to save package file : class not registered

Problem:

Sometime a user may encounter an error like below while creating a SSIS package from BIDS.

Error creating package

------------------------------
ADDITIONAL INFORMATION:

Failed to save package file "C:\Documents and Settings\UserName\Local Settings\Temp\tmp87.tmp" with error 0x80040154 "Class not registered".

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

Solution:

To solve this try to register below DLLs by typing below commands in CMD promopt:-

regsvr32 msxml3.dll
regsvr32 msxml4.dll
regsvr32 msxml6.dll

Copy/Rename a file using File System Task in SSIS

This is a reference answer to a post on MSDN forum but is very useful

Question: While copying and renaming a file to a dynamic location through File System Task using variable throws an error

Error: Failed to lock variable "c:\test\test_200904202009.txt" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

OR

How to copy and rename a file to a dynamic location using SSIS?

Solution:

I will move and rename a file "test.txt" from c:\ to c:\backup with new name appended with date.

1) Create three variables

Src_File = c:\test.txt

Dest_File = test

Dest_Dir = c:\backup

2) In connection Manager create a File Connection with name as DestinationConn

3) In Expression property of DestinationConn use ConnectionString and provide following expression

@[Dest_Dir] + "\\" + @[Dest_File] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt"

this will set the destination path and new name for the file

4) Create a File System task and configure like :

5) Execute the package

This Package can be downloaded from here.

To run the package create a file named "test.txt"  and a folder "backup"at C:\

Thanks!!

Please leave a comment.

Trail Period is Over - the funny side

Column length in Excel source

Here is one very common warning with using Excel in SSIS

Whenever we use Excel file in SSIS, it takes default length of each column as 255. This results in having a warning if we try to map this column with column having length less than 255. We can avoid this warning my setting column length of the Excel source.

Right click to Open Excel source in Advance editor and set lenght of the column as depicted in the figure.

Getting Daily Feed in SSIS

Many time there comes a situation when you need to use a daily feed kind of file which is copied in a fixed directory and we need to use the file for data import.

Below I will show an approach to automate the process so that SSIS package would check if the today's file is present or not and if present then do the data import.

For Example
File name format : FileNameDate (FileName03022009.xls)
Fixed Directory : D:\Abc

1.Create a Variable "FileName" This will hold the path and name of the daily feed file.

2. Use script task. specify readwrite variable as "FileName"

3. Coding
a) Configure FileName to have path and name of the daily feed file.
Dts.Variables("FileName").Value = "D:\Abc\FileName" _ & Date.Today.Month.ToString.PadLeft(2, "0"c) & Date.Today.Day.ToString.PadLeft(2, "0"c) _ & Date.Today.Year & ".xls"

b) check if file is present or not. (Fail the package if file is not present)
'check if file is present
Dim Fi As New FileInfo(CStr(Dts.Variables("FileName").Value)) If (CBool(Fi.Exists().ToString())) Then Dts.TaskResult = Dts.Results.Success Else MsgBox(CStr(Dts.Variables("FileName").Value) & " is not present") Dts.TaskResult = Dts.Results.Failure End If

4) Create a Excel Connection Manager and in Expression property specify


5) Use the Excel Connection Manager in Excel source of DATA FLOW
6) Execute