Create a Dyanamic Excel destination file with a filename base on the date

Problem Statement:
Create a Dynamic Excel destination file with a file name base on the date 


Things which we have to remember:
1.Delay validation of DFT(data flow task) should be True.
2.Should not run at 64 Bit run time in short (Run64Bit Run time should be False)
3.Check weather your drive have permission to create the excel file in the folder othere wise it will throw you below error:
 [Error: [Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection. ]

Solution steps:
1.Create a package level variable "XLFileRootDir" as string and set it to the root directory where you want to create excel file.
 Example: D:\
2.Create a package level variable "Client_detail" as string and set it to the file name,which we will use to create the dynamic file name.

 Example: "Client_Detail"
3. Create an Excel connection in the connection manager. Browse to the target directory and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.Later we will replace the file name with our dynamic generated file name.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@[User::XLFileRootDir] + @[User::Client_Details]+(DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an exl file like Client_detail_05072011.xls.

5. Add a SQL task to package and double click to edit.

In the general tab, set 'ConnectionType' to 'Excel'.

For 'SQLStatement', enter the create table SQL to create destination table.

For example:

CREATE TABLE Employee_Detail (

     FirstName NVARCHAR(50)

    ,MiddleName NVARCHAR(50)

    ,LastName NVARCHAR(50)

    ,BirthDate NVARCHAR(50)

    ,Gender NVARCHAR(50)

    ,EmailAddress NVARCHAR(50)

)

Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.

7. Connect this to Excel destination. 

In the destination editor, select the Excel connection in the manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on new button and paste the create table command from Step 5.

Map the columns appropriately in the mappings tab and you are done.

Set the Connection Manager Values using the Script Task

We can set the Connection manager Settings while running the Package.We can save the Connection information into the config file ,and from the config file we can read the values and use in the package.

For Example :check the below sample config File.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

            <section name="TS_Contract_Interface.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

    </sectionGroup>

    </configSections>

How to Set FTP Connection Manager Password Using Script Task(VB)

1.Create the FTP Connection Manager and Enter the "Server name"  and "UserName".Here the Connection manager name is CNRL_FTP Connection Manager

 

2.Public Sub Main()

        Dim FTPConnectionManager As ConnectionManager

        'Set variable to an existing connection manager

        FTPConnectionManager = Dts.Connections("CNRL_FTP Connection Manager")

        'Set connection manager property "ServerPassword"

        FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, "cnrlPUftp")

            Dts.TaskResult = ScriptResults.Success

      End Sub

 

Insert Row Records into Columns.

The Source data is Like:store in Retention_Import_Staging Table

Now We have to convert above data into below format and load data into Retention_Dates table

By Using the Store Procedure we implement the above task:

IF EXISTS (SELECT       *

                              FROM dbo.sysobjects

                              WHERE       id = object_id(N'[dbo].[TS_pCN_Dates_Load]')

                              AND   OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

      DROP PROCEDURE [dbo].[TS_pCN_Dates_Load]]

      PRINT 'Deleted script ''dbo.TS_pCN_Dates_Load'''

END

GO

SET   QUOTED_IDENTIFIER ON

GO

SET   ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[TS_pCN_Dates_Load]

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      DECLARE @RowData varchar(4000)

      DECLARE @Col1 varchar(50)

      DECLARE @Col2 varchar(50)

      DECLARE @Col3 varchar(50)

      DECLARE @Col4 varchar(50)

      DECLARE @SplitOn varchar(3)

      DECLARE @Row_id int    

      DECLARE @array_text varchar(4000)

      DECLARE @Error_fg varchar(2)

SET @Error_fg = (select top 1 error_fg from SFI_Retention_Import_Staging where error_fg ='Y')

IF ISNULL(@Error_fg,'')=''

BEGIN

      ----------------fetch records from SFI_Retention_Import_Staging table using cursor------------------

      DECLARE Retention_Cursor CURSOR FOR

      SELECT Row_id,array_text from Retention_Import_Staging

      OPEN Retention_Cursor

      FETCH NEXT FROM Retention_Cursor INTO @Row_id,@array_text

      WHILE @@FETCH_STATUS = 0

      BEGIN

           

      SET @SplitOn = ','

      SET @RowData = @array_text + ','

      SET @Col1 = NULL

      SET @Col2 = NULL

      SET @Col3 = NULL

      SET @Col4 = NULL 

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col1 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE 

      BEGIN

            SELECT @Col1 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

     

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col2 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE

      BEGIN

            SELECT @Col2 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

      IF (@RowData = '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

      While (Charindex(@SplitOn,@RowData)>0)

      Begin

            SET @Col3 = NULL

            SET @Col4 = NULL

            SELECT @Col3 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            if @col3 = ''

            set @col3 = NULL

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

                 

            IF (Charindex(@SplitOn,@RowData)>0)

            BEGIN

                  SELECT @Col4 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

                  if @col4 = ''

                  set @col4 = NULL

                  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

            END

            INSERT INTO dbo.Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            VALUES(@Col1,@Col2,@Col3,@Col4)

      End

      IF (@RowData <> '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

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

FETCH NEXT FROM Retention_Cursor INTO @ROW_ID,@array_text

      END

      CLOSE Retention_Cursor

      DEALLOCATE Retention_Cursor

      ---update trigger_date field------------------

      UPDATE Retention_Dates

      SET TRIGGER_DT= (SELECT convert(VARCHAR(10),TRIGGER_DT,101))

END  

      DELETE FROM Retention_Dates

      WHERE ROW_ID IN(

            SELECT row_id

            from

                  (

                  Select row_id,ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT,rank() over (partition by ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT order by row_id) as rnk

                  from Retention_Dates

                  )aa

            WHERE aa.rnk > 1)

END

How to Write a Store procedure for "Search"

IF EXISTS (SELECT *  FROM dbo.sysobjects   WHERE id = object_id(N'[dbo].TS_pRP_Filtered') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

      DROP PROCEDURE [dbo].TS_pRP_Filtered

END

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE dbo.TS_pRP_Filtered

      @owner_co_id            int,

      @user_id                int,

      @Document_id            int,

      @Document_version int,

      @Document_type          int,

      @SQLFilter              varchar(4000) = NULL,

     

      @ContractNo               nvarchar(100) = NULL,

      @SelectedSuppNo       nvarchar(100) = NULL,

      @ContractType             nvarchar(100) = NULL,

      @ContractDescription  nvarchar(100) = NULL,

      @ContractManager      nvarchar(100) = NULL,

      @ContractorName           nvarchar(100) = NULL,

      @OrgUnit                  nvarchar(100) = NULL,

      @ContractStartDateEval1 nvarchar(100) = NULL,

      @ContractStartDate1 nvarchar(100)     = NULL,

      @ContractStartDateEval2 nvarchar(100) = NULL,

      @ContractStartDate2 nvarchar(100)     = NULL,

      @ContractStartEndEval1 nvarchar(100)  = NULL,

      @ContractEndDate1 nvarchar(100)       = NULL,

      @ContractStartEndEval2 nvarchar(100)  = NULL,

      @ContractEndDate2 nvarchar(100)       = NULL,

      @Language nvarchar(100)                     = NULL

     

AS

DECLARE @SQLString nvarchar(4000)

Declare @SQL nvarchar(max)

DECLARE @doc_id             int

DECLARE @doc_vers_id    int

DECLARE @doc_type_id    int

DECLARE @CRole                Varchar(50)

SET @doc_id = @Document_id

SET @doc_vers_id = @Document_version

SET @doc_type_id = @Document_type

SET NOCOUNT ON

IF (@owner_co_id IS NOT NULL)

BEGIN

                  Set @SQL = 'SELECT Distinct

                              ch.po_contract_id,

                              ch.supplement_no,

                              0 AS selected_row,                                   

                              ISNULL(ch.po_contract_no,'''') AS po_contract_no,

                              ISNULL(ch.start_dt,'''') AS start_dt,

                              ISNULL(ch.end_dt,'''') AS end_dt,

                              ISNULL(rct.contract_type_cd,'''') AS contract_type_cd,

                              ISNULL(OU.org_unit_cd,'''') AS org_unit_cd,          

                              ISNULL(th.template_cd,'''') AS template_cd

                       

                  FROM CN_Contract_Header ch

                              INNER JOIN CN_Ref_Contract_Type rct

                                    ON ch.contract_type_id = rct.contract_type_id

                              Inner join CM_Ref_language RL

                                    ON RL.language_id = ch.language_id

                              INNER JOIN cm_ber_users U

                                ON CH.contracting_user_id = U.user_id                          

                              INNER JOIN CN_Template_Org_Unit tou

                                    ON tu.org_unit_template_id = tou.org_unit_template_id

                              INNER JOIN CN_Template_Header th

                                    ON tou.template_id = th.template_id

                              INNER JOIN CM_BER_User_Role_Membership urm

                                                ON urm.user_id = u.user_id

                              LEFT OUTER JOIN CM_UDF_Value uv1

                                    ON ch.po_contract_id = uv1.record_id

                                          AND ch.supplement_no = uv1.record_version_id

                                          AND uv1.custom_info_id = (SELECT custom_info_id FROM CM_UDF_Custom_Info

                                                                                    WHERE column_name = ''Contract_Classification''

                                                                                          AND extended_table_cd = ''Contract_Hdr''

                                                                                          AND owner_co_id = '''+CAST(@owner_co_id AS varchar) +''')

                             

                              LEFT OUTER JOIN CM_UDF_Value uv9

                                    ON ch.po_contract_id = uv9.record_id

                                          AND ch.supplement_no = uv9.record_version_id

                                          AND uv9.custom_info_id = (SELECT custom_info_id FROM CM_UDF_Custom_Info

                                                                                    WHERE column_name = ''Approver''

                                                                                          AND extended_table_cd = ''Contract_Hdr''

                                                                                          AND owner_co_id = '''+CAST(@owner_co_id AS varchar) +''')

                                                       

     

                        WHERE CH.supplement_no = (SELECT max(supplement_no)

                                                              FROM CN_CONTRACT_HEADER

                                                              WHERE po_contract_id = CH.po_contract_id and deleted = ''N'')

                                AND CH.deleted = ''N''

                                AND  1=1'

                               

                        IF ISNULL(@ContractNo,'') <> ''

                           SELECT @sql = @sql + ' AND ch.po_contract_no LIKE ''%'+@ContractNo+'%'''

                        IF ISNULL(@SelectedSuppNo,'') <> ''

                           SELECT @sql = @sql + ' AND ch.supplement_no = '+@SelectedSuppNo

                                                                                                                         

                        IF ISNULL(@ContractType,'') <> ''

                           SELECT @sql = @sql + ' AND rct.contract_type_cd LIKE ''%'+@ContractType+'%'''          

                                                                                                                          

                        IF ISNULL(@ContractDescription,'') <> ''

                           SELECT @sql = @sql + ' AND ch.po_contract_descr LIKE ''%'+@ContractDescription+'%'''

                        IF ISNULL(@ContractManager,'') <> ''

                           SELECT @sql = @sql + ' AND u.user_id = '+@ContractManager

                        IF ISNULL(@ContractorName,'') <> ''

                           SELECT @sql = @sql + ' AND CP.company_name LIKE ''%'+@ContractorName+'%'''

                        IF ISNULL(@OrgUnit,'') <> ''

                           SELECT @sql = @sql + ' AND OU.org_unit_id = '+@OrgUnit                    

                        IF ISNULL(@ContractStartDateEval1,'') <> '' AND  ISNULL(@ContractStartDate1,'') <> ''

                           SELECT @sql = @sql + ' AND ch.start_dt '+@ContractStartDateEval1+' '''+@ContractStartDate1+''''

                       

                        IF ISNULL(@ContractStartDateEval2,'') <> '' AND  ISNULL(@ContractStartDate2,'') <> ''

                           SELECT @sql = @sql + ' AND ch.start_dt '+@ContractStartDateEval2+' '''+@ContractStartDate2+''''

                        IF ISNULL(@ContractStartEndEval1,'') <> '' AND  ISNULL(@ContractEndDate1,'') <> ''

                           SELECT @sql = @sql + ' AND ch.end_dt '+@ContractStartEndEval1+' '''+@ContractEndDate1+''''

                       

                        IF ISNULL(@ContractStartEndEval2,'') <>'' AND  ISNULL(@ContractEndDate2,'') <> ''

                           SELECT @sql = @sql + ' AND ch.end_dt '+@ContractStartEndEval2+' '''+@ContractEndDate2+''''        

                        IF ISNULL(@Language,'') <> ''

                           SELECT @sql = @sql + ' AND RL.language_id = '+@Language