1

I receive an excel file with the following info:

SQL SERVER          SCHEMA  TABLENAME
AdventureWorksDW    Dbo     DimOrganization
AdventureWorksDW    Dbo     ...
...                 ...     ...

I want to export all columns information from the above TABLENAME from SQL Server into excel sheet using SSIS. The excel filename should be the TABLENAME above. One TABLENAME for one excel sheet, so the package should be dynamic.

Output: DimOrganization.xlsx (TABLENAME above, etc.)

Number  Name                    Type            Required    Source
1       OrganizationKey         int             N           AdventureWorksDW.dbo.DimOrganization
2       ParentOrganizationKey   int             Y           AdventureWorksDW.dbo.DimOrganization
3       PercentageOfOwnership   nvarchar(16)    Y           AdventureWorksDW.dbo.DimOrganization
4       OrganizationName        nvarchar(50)    Y           AdventureWorksDW.dbo.DimOrganization
5       CurrencyKey             int             Y           AdventureWorksDW.dbo.DimOrganization

In order to get the column info above from SQL, my SQL Task will be:

SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) [Number],
COLUMN_NAME Name, 
DATA_TYPE +
CASE 
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) +')' 
END Type,
    CASE IS_NULLABLE
                WHEN 'NO' THEN 'N'
                ELSE 'Y'
            END AS Required,
IC.TABLE_CATALOG+'.'+IC.TABLE_SCHEMA+'.'+Ic.TABLE_NAME [Source]
FROM AdventureWorksDW.INFORMATION_SCHEMA.COLUMNS IC
WHERE TABLE_NAME = 'DimOrganization'

What should I do in SSIS to create each excel file for each TABLENAME above without using script?

I believe I need to add a variables and parameters to assign the WHERE TABLE_NAME = ? This way, if I change the TABLE_NAME variable and run it, if valid, it will retrieve the info from SQL, input metadata info into excel file and save the excel as filename? Any thought?

Peter Grins
  • 19
  • 4
  • This may helps: [How to Map Input and Output Columns dynamically in SSIS?](https://stackoverflow.com/questions/54781017/how-to-map-input-and-output-columns-dynamically-in-ssis) – Yahfoufi Aug 28 '19 at 05:50
  • YMMV. But my experience with SSIS is that: dynamic columns / tables and SSIS do not mix well. I would normally look at other technologies like C#. SSIS wants the meta data at the design time. – Subbu Aug 28 '19 at 08:44

0 Answers0