SSIS-ExecuteSqlTask with ADO.NET Connection

Hi,
ADO is a Microsoft own driver,so it is a way to connect and fast to execute sql statements than OLEDB.
By using ADO.NET Connection we can set the parameters as described below.

strADOConnectionString is defined like

Data Source=localhost;Initial Catalog=AdventureWorksDW;Integrated Security=True;

General Tab:-

ConnectionType ADO.NET
Connection ADO.NET
SqlSourceType Direct
SQLStatement uspETLLoadPackage
IsQueryStoredProcedure True


ParameterMapping Tab:-

VariableName Direction Data Type ParameterName

System::PackageName Input String @PkgName
System::ContainerStartTime Input DateTime @ExecStartDT
User::intPkgExecKey Output Int32 @intPkgExecKey
User::intDimAuditKey Output Int32 @intdimAuditKey

Thanks.

MOSS-2007

Hi,

By using MicrosoftOfficeSharepointServer,Using Windows SharePoint Services we can use it as a Platform for Building Collaborative Applications.
See More Details:
http://office.microsoft.com/en-us/sharepointserver/default.aspx
http://msdn.microsoft.com/en-us/magazine/cc163948.aspx
Download from the below link:
http://www.microsoft.com/downloads/details.aspx?FamilyId=2E6E5A9C-EBF6-4F7F-8467-F4DE6BD6B831&displaylang=en
How to Install MOSS’07 :
http://blogs.msdn.com/martinkearn/archive/2007/03/28/how-to-install-sharepoint-server-2007-on-a-single-machine.aspx
Free Videos:
http://www.learnsharepoint.com/FreeVideos/
http://www.learnitfirst.com/FreeVideos/ListOfFreeVideos.aspx
http://wareseeker.com/free-moss-2007-1.0/http://www.click2learn.ch/Free%20Video/Forms/DispForm.aspx?ID=68

Microsoft Dynamics Axapta

Hi,
Dynamics Axapta (Ax 4.0) is Microsoft's leading ERP offering for Enterprise segment customers. At HCL, we believe in creating future-centric services and solutions around the product that aligns with the partner's strategy and enhances our ability to deliver value to customers.
See More Details:
http://www.microsoft.com/dynamics/AX/default.mspx
Download from the below link:
http://www.mibuso.com/downloads.asp?category=axaptademos
How to Install Axapta :
Setup and Customization of an Axapta Enterprise Portal.
Free E-Books:
http://www.netbks.com/database/dynamics-ax-a-guide-to-microsoft-axapta_4663.html

SSIS - How to Move variables/parameters Value from Child Package to Master Package

Hi,
We required some time to pass variable values from child to Master.
we can get the value from Master to child is easily from ParentPackage Configuration.
But In the case of vice-versa, just pass the required variable in the script task and assigned the child package variable to Master Package variable at the child package level.
Here no required to create Master Package Variable.
When you execute from Master,we can get value directly from Child.
Thanks.
For More Details:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/29/passing-a-value-back-and-forth-from-a-parent-package-to-a-child-package-in-ssis.aspx

SSIS - Integrated With Powershell

MVP Jamie Thomson: What are the respective strengths of SSIS and PowerShell for importing data etc?
Scott Whigham at LearnItFirst.com: How to Execute SSIS Packages In Windows PowerShell (VIDEO)

SSIS - How to execute Package from CommandPrompt

Hi ,
already I posted how to execute a SSIS package by using command line.
Once again am adding some more points to make easy.
SSIS - HOW TO EXECUTE A PACKAGE THROUGH THE CMD LINE
It a simple to execute the package from cmd window without using MSVS-2005.
Just open the command prompt.
Type dtexec command
And type the package name along with the /file prefix.

Example:
C:\>DTEXEC /file Export.dtsx
So it means Export.dtsx is stored in the C:\ directly.
Just browse your SSIS-Package-folder and add /file and the package name along with .dtsx extension.

See for more details:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2008/08/31/dtexec-exe-what-are-you-doing.aspx

SSIS - Packages Ordering in Visual Studio 2005 Solution Explorer Window

Hi,
Am just describing how to order our packages in the SolutionExplorer Window.
All our Package list will be stored in the .dtproj file.
Basically it is a xml file.
Just open with notepad and arrange all Package tags list in the order of alphabetic.
Save it and reopen the solution once again.
Done.

Thanks.

See for more details:

http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2008/05/16/ordering-ssis-packages-in-visual-studio-2005.aspx

Problems installing SQL Server 2005 on Vista

Problems installing SQL Server 2005 on Vista

We have a PC with Windows Vista installed on it and are havingproblems installing the Developer Edition of Sql Server 2005 on it.The installation completes but some components will not install. Vista prompts with the incompatability message and suggests that weget the V2 service pack, which we have.

Soution:

I installed SQL Server2005 Developer Edition + SP2
I've seen pop up a few times so I thought I'd share a few quick tips / tricks to getting SQL Server 2005 to work on Vista.
If you see an error saying "Microsoft Internet Information Services (IIS) is either not installed or is disabled.", but you're sure you did in fact install IIS7, you are not alone. There are several required IIS components for SQL Server to install properly on Vista and if you don't have the complete set you will see this error.
Download - Microsoft IIS7 From below Link:

http://www.softpedia.com/progDownload/Application-Request-Routing-for-IIS-Download-113976.html


Download - Microsoft SQL Server 2005 Service Pack 2 From below Link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en

SSIS - Derived Column Transformation

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.
You can use this transformation to perform the following tasks:
Concatenate data from different columns into a derived column. For example, you can combine values from the FirstName and LastName columns into a single derived column named FullName, by using the expression FirstName + " " + LastName.
Extract characters from string data by using functions such as SUBSTRING, and then store the result in a derived column. For example, you can extract a person's initial from the FirstName column, by using the expression SUBSTRING(FirstName,1,1).
Apply mathematical functions to numeric data and store the result in a derived column. For example, you can change the length and precision of a numeric column, SalesTax, to a number with two decimal places, by using the expression ROUND(SalesTax, 2).
Create expressions that compare input columns and variables. For example, you can compare the variable Version against the data in the column ProductVersion, and depending on the comparison result, use the value of either Version or ProductVersion, by using the expression ProductVersion == @Version? ProductVersion : @Version.
Extract parts of a datetime value. For example, you can use the GETDATE and DATEPART functions to extract the current year, by using the expression DATEPART("year",GETDATE()).
IntroductionIn this article in an ongoing introductory series about SSIS, we will learn how to derive a column from existing data that is being processed. The Derived Column component is another component that is widely used in SSIS packages. It is versatile enough to accommodate a variety of needs.
Scenario
Since we are building on top of what we have already learned, we will use the previous article’s code to learn how to use the Derived Column component. As before, we have a list of credit card transactions and are dumping the transactions to a daily report file. Let’s say for instance, that this company the processes these transactions get a 5% processing fee of the credit card purchase. This processing fee needs to be in the daily report. This is where the Derived Column component comes into play
Implementation
Create a new package in the SSIS project. Copy the Data Flow component from the Export package into the Control of the new project. Change the name of the project to Derived. Go to the data flow tab and delete the connection between the Merge output and the Flat File Destination component. Rebuild the Vendor A and Vendor B connections then configure their respective Flat File Sources to use those connections. Next find the Derived Column component in the toolbox and drag it into the work area. Connect the output of the Merge to the Derived Column.
[Click to see full-size]
Double click the Derived Column component to configure the component’s properties. First we need to give the new column a name. Since it represents the processing charge we will call it ProcessingCharge. Tab to the next column and make sure that we are adding it as a new column. We can replace a column that we know we do not want by selecting the unwanted column in the drop down list. Tab over to the Expression column. The processing charge is going to be a compute column based on the amount of the credit card purchase. So, in the upper left hand box, expand the list of columns and drag the Amount column down to the Expression field in the table.
[Click to see full-size]
As you can see from the right hand box there are a few ways to manipulate data. Expressions are used in several different spots in SSIS to calculate and manipulate data as it flows through the SSIS package. Since the Amount data type is coming in as string we need to convert it to decimal so we can perform some basic math on it. So expand the Type Casts tree and find the decimal data type, (DT_Decimal), and drag it in front of the Amount column in the Expression field in the table and tab over the Data Type column.
[Click to see full-size]

The text is red since the expression results in an error. Inside the decimal type cast, there is a field called scale that is inside a set of <<>>. This needs to be filled out which in this case it will be set to 1. Now that the Amount column is in a data type we can perform some math on, we will then multiple it by .05. You can either type in * or you can expand the Operators tree and drag the appropriate operator to the Expression field.
[Click to see full-size]
Click OK. Since the output of the data, the structure of the output file needs to be changed as well. Use the file included in the source or you can manually add the ProcessingCharge column at the end of the first line in the file. Create a flat file connection in Connection Manager to be used with altered file. Now configure the Flat File Destination component to use the new connection. Go to the Mappings tab to make sure that the data will be processed into the file correctly.
[Click to see full-size]

Click OK then connect the output of the Derived Column to the Flat File Destination.
[Click to see full-size]

Testing the PackageLet’s see if the new column shows up in the new file. Run the package.
Once the package has completed running, go check the flat file to see if the derived column showed up in the transaction report.
More Expressions:-
(DT_STR,4,1252)DATEPART("yyyy",Derived.Date) + RIGHT("0" + (DT_STR,4,1252)DATEPART("mm",Derived.Date),2) + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",Derived.Date),2)
ROUND((DT_DECIMAL,2)DATEDIFF("mi",(DT_DBTIMESTAMP)Derived.Date,(DT_DBTIMESTAMP)CreatedDate) / (DT_DECIMAL,2)60,2)
DATEDIFF("HH",(DT_DBTIMESTAMP)Derived.Date,(DT_DBTIMESTAMP)GETDATE())
ROUND((DT_DECIMAL,2)Derived.Column / (DT_DECIMAL,2)60,2)

What have we learned?
How to configure a Derived Column component.How to use expressions to manipulate data inside of a package.

For More Details:-

http://www.programminghelp.com/database/sqlserver/sql-server-integration-services-derive-column/

SSIS - HOW TO EXECUTE A PACKAGE THROUGH THE CMD LINE

Hi All,
In Production environment,we should execute the packages by creating the jobs.
Jobs are internally execute the package by using the CommandLine Prompt.
This problem occurs when one of the following conditions is true:
The user account that is used to run the package under SQL Server Agent differs from the original package author.
The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
The package may not run in the following scenarios:
The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.
A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific.
A task or a connection manager requires that the current user account has correct permissions.
To resolve this problem, use one of the following methods. The most appropriate method depends on the environment and the reason that the package failed.
Method 1: Use a SQL Server Agent proxy account
Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions. This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles.
Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.
Method 4: Use SSIS Package configuration files
Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.
Method 5: Create a package template
For a long-term resolution, create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

The following is an example of a command line:dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1
" /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT



See the below link for more details:

http://bi-polar23.blogspot.com/2008/06/ssis-and-sql-server-agent-choosing.html

Unit Testing in SSIS

Hi ,in SSIS Unit testing is important and it’s basically testinf from Developer end.
Am posting a brief introduction about the Unit testing.
ssisUnit is a unit testing framework for SQL Server Integration Services. ssisUnit is a unit testing framework for SQL Server Integration Services. It is loosely based on the xUnit family of unit testing frameworks, but is tailored to better support the SSIS environment. One of the bigger differences is that you do not have to write code to create the unit tests. Instead, we have taken a declarative approach to specifying the unit tests. The test information is all stored in XML files. Since many SSIS developers do not have a background in coding traditional applications, we felt it was important to deliver something that did not require an understanding of .NET development to use.ssisUnit supports task level testing in SSIS. Any control flow task can have a set of tests created around it. This level of granularity in testing can make testing complex packages much easier.

See For More Details:
http://stevemchugh.blogspot.com/2007/05/unit-testing-ssis.html

Unit Test Structure

32 bit vs 64 bit problem in ssis

Hi, am describing a brief introduction about the issue of 32bit Vs 64bit in SSIS.

With the introduction of 64-bit processors, there seem to evolve two main flavors of this new processor architecture : x86-64 and IA64.
First, let's take a look at different flavors of 64-bit, namely x86-64 and IA-64. And then, I'll explain what WOW64 feature is, and how Windows applications, including SSIS, can take advantge of this Windows feature. Finally, I'll explain what components of SSIS are compiled in what architectures, and how to use them in 64-bit.
64-bit Flavors: x86-64 and IA-64
In computing, IA-64 (short for Intel Architecture-64) is a 64-bit processor architecture developed cooperatively by Intel Corporation and Hewlett-Packard (HP), and implemented in the Itanium and Itanium 2 processors. The goal of IA-64 was to produce a "post-RISC era" architecture that would address some of the key challenges faced by older architectures, to enable more efficient performance scaling in future processor designs.
AMD's AMD64 architecture, first delivered in 2003, found success in the marketplace, the Itanium was delayed, and Intel developed and announced the EMT64 architecture, which is very similar to AMD64. Both architectures are 64-bit upgrades to the x86 architecture, and the two are often referred to generically as the "x86-64 architecture". In 2006, Intel delivered x86-64 processors based on the Intel Core microarchitecture.
What is WOW64?
Windows XP x64 Edition uses a technology named WOW64, which permits the execution of 32-bit x86 applications. It was first employed in Windows XP 64-bit Edition (for the IA-64 (Itanium)), but then reused for the “x64 Editions” of Windows XP and Windows Server 2003.
Since the X86-64 architecture includes hardware-level support for 32-bit instructions, WOW64 simply switches the process between 32- and 64-bit modes. As a result, X86-64 architecture microprocessors suffer no performance loss when executing 32-bit Windows applications. On the IA-64 architecture, WOW64 was required to translate 32-bit x86 instructions into their 64-bit IA-64 equivalents—which in some cases were implemented in quite different ways—so that the processor could execute them.
How to develop and deploy SSIS applications on 64-bit machines?
Although 32-bit applications can be run transparently, the mixing of the two types of code within the same process is not allowed. A 64-bit application cannot link against a 32-bit library (DLL) and similarly a 32-bit application cannot link against a 64-bit library. This may lead to the need for library developers to provide both 32- and 64-bit binary versions of their libraries.

SSIS supports these two different flavors of 64-bit in various levels.

Availbility of SSIS executables in 32&64bit architectures
Architecture
SSIS runtime (dtexec)
BI Studio
DTS2000 Support (invoker)
x86
Yes
Yes
Yes
x86-64
Yes*
No*
No*
IA-64
Yes*
No**
No*




*SSIS installs 32bit binaries to take advantage of WOW64.
**SSIS does not install 32bit binaries, and thus this component cannot run even in 32-bit mode.
BI-Studio
Since the BI-Studio is only availble in x86, due to the limitataion of mixing the two types of code mentioned above, BI-Studio can only load 32-bit drivers (i.e. if you have a 64bit Oracle driver, it will not show up in the designer, that's why you'd need a 32-bit version of the Oracle driver during the design time).
Another point about the BI-Studio is that, although it is available in x86-64 and can be invoked using the WOW64 feature of windows, it is not available in IA-64 machines, and thus cannot be used (see above table). This limitation is enforced mainly because Visual Studio 2005 is not supported in WOW64 mode on IA-64 platforms. Since BI-Studio is derived from Visual Studio 2005, to avoid ending up in an unsupported platform, SSIS does not deploy 32-bit BI Studio on IA-64 platforms.
However, SSIS runtime is available in both of these platforms and therefore IA-64 machines can be used in production even without the BI-Studio installed on them.
SSIS Runtime (dtexec.exe)
SSIS runtime is available in both flavors of 64-bit (see above table), along with the 32-bit binaries to enable running SSIS in 32-bit process space, using the WOW64 feature of Windows. If your SSIS application has a dependency on a 32-bit binary/driver/connector/script/DTS2000 Package, running the 64-bit dtexec.exe will fail to load dependencies due to the limitation on mixed-mode application linking.
So, when invoking the 64-bit dtexec.exe, make sure that all the dependencies of your SSIS application are available in 64-bit. This might introduce some limitations around unavailbility of some OleDB providers on 64-bit, or running DTS2000 packages, which can only be done in 32-bit. In such cases, you'll have to use the 32-bit dtexec.exe on a 64-bit machine.
Program Manager - SQL Server Integration Services - Microsoft
More reading: http://msdn2.microsoft.com/en-us/library/ms141766.aspx

How to retrieve files from folder and records from Table by using ForEachLoop Container:-

How to retrieve files from folder and records from Table by using ForEachLoop Container:-
Hi,
By using ForEachLoop Container we can repeat loop and send all files to execution.
1.For Each File Enemurator – Files from Folder
a.In the Collection tab first we can browse file folder and we can set the files along with extension like Screen 1.
b.we can set the variable in the Variablemapping tab,this variable will read the file names one by one from the loop like Screen2.
Screen1

Screen2

2.For Each ADO Enemurator – Records from Table
a.First we can select the columns from the table using ExcecutesqlTask,and we can assign these values to local variables and fullresultset to a system.Object Type variable like Screen 1,Screen 2 and Screen 3.
b.In the ForEachloopContainer Collection tab we can select that system.Object Type variable to ADOobjectSource_Variable like Screen 4.
c.We can set the local variable in the Variablemapping tab,this variable will read the columns one by one from the loop like Screen 5.
Screen 1

Screen 2

Screen 3
Screen 4
Screen 5