C# Messagebox within SSIS

 

Add a script component to the error handler within the package and pass the error message as a readonly  variable.

Check the existence of indexes before trying to create them

Here is the standard template to check existence of an index within the database you are working on:

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

-- where MyTable and MyIndex are replaced with appropriate values

-- and 'IndexID' is the non-replaced string value actually used

   If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null

              CREATE INDEX index_name ON table_name (column1, column2, ...)

 -- for example:

       If IndexProperty(Object_Id('dbo.TEST'), 'IX_TEST_State', 'IndexID') Is  not Null

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END

But what if you're trying to create a stored proc to do this on a database other than where your stored proc exists.... like DB A is loaded every night by restoring a backup off another server and won't retain it's stored procedures when it's overwritten?  Unfortunately, the usual and common method documented above will not traverse to another database and will always report it was not found, thus trying to create the index anyway.  Here is a method that will query the other DB's internals without fail:

IF NOT EXISTS (

        SELECT si.name

        FROM DatabaseName>.sys.indexes si

        INNER JOIN <DatabaseName>.sys.objects so

        ON si.object_id = so.object_id

        WHERE si.name = '<IndexName>'

        AND so.name = '<TableName>'

)

BEGIN

        CREATE INDEX [<IndexName>] ON [<DatabaseName>].[<Schema>].[<TableName>] 

        ([column1]) ,,,

END

SQL Server Window Functions

Okay, so if you got this far then I assume you know that the picture above is not exactly what you're seeking.
Sometimes when you find something that works for you, it's better to just link to the source, like I've done here. The following is a  straight forward and easy to follow explanation of the Window Functions available in SQLServer:


https://www.sqlservertutorial.net/sql-server-window-functions/

Output values as single line CSV

declare @yourtable  table

([ID] int, [Date] datetime)

;

 

 

INSERT INTO @yourtable

([ID], [Date])

VALUES

(756, '2011-08-29 00:00:00'),

(756, '2011-08-31 00:00:00'),

(756, '2011-09-01 00:00:00'),

(756, '2011-09-02 00:00:00')

;

INSERT INTO @yourtable

([ID], [Date])

VALUES

(956, '2011-08-29 00:00:00'),

(956, '2011-08-31 00:00:00'),

(956, '2011-09-01 00:00:00'),

(956, '2011-09-02 00:00:00')

;

 

-- example 1-----------------------------------------------------------------

select

distinct t1.id,

STUFF((SELECT ', ' + convert(varchar(10), t2.date, 120)

   FROM @yourtable t2

   where t1.id = t2.id

   FOR XML PATH ('')), 1, 1, ''

)  AS date

from @yourtable t1;

 

 

-- query to return every table and column in order

select isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,max(SI.rows) as RecCount
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
from INFORMATION_SCHEMA.COLUMNS isc
join INFORMATION_SCHEMA.TABLES ist
on isc.TABLE_NAME = ist.TABLE_NAME
join SYSINDEXES SI
on ist.TABLE_NAME = OBJECT_NAME(SI.ID)
where isc.TABLE_CATALOG = 'xxxx'
and isc.TABLE_SCHEMA = 'dbo'
and ist.TABLE_TYPE = 'Base Table'
group by
isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
order by TABLE_NAME, ORDINAL_POSITION

-- query to determine to find all the keys in the database, which columns are involved, and report if they are combined keys (ColumnCount)


;with CTE_SingleKey as 
(select
'Single Column Key' as KeyType
,COUNT(scu.column_name) as ColumnCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name)
)
,CTE_MultiKey as 
(select
'Multi Column Key' as KeyType
,COUNT(scu.column_name) as ColumnsCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name) > 1
)

select mkey.*
,scu.COLUMN_NAME
from CTE_MultiKey mkey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on mkey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME

union

select skey.*
,scu.COLUMN_NAME
from CTE_SingleKey skey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on skey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME
order by
TABLE_NAME
,CONSTRAINT_NAME
,COLUMN_NAME

-- query to find all foreign keys and referenced tables/columns

SELECT obj.name AS FK_NAME,
schem.name AS [schema_name],
Table1.name AS [table],
Columns1.name AS [column],
table2.name AS [referenced_table],
Columns2.name AS [referenced_column]
FROM sys.foreign_key_columns fkCol
INNER JOIN sys.objects obj
ON obj.object_id = fkCol.constraint_object_id
INNER JOIN sys.tables Table1
ON Table1.object_id = fkCol.parent_object_id
INNER JOIN sys.schemas schem
ON Table1.schema_id = schem.schema_id
INNER JOIN sys.columns Columns1
ON Columns1.column_id = parent_column_id AND Columns1.object_id = Table1.object_id
INNER JOIN sys.tables table2
ON table2.object_id = fkCol.referenced_object_id
INNER JOIN sys.columns Columns2
ON Columns2.column_id = referenced_column_id
AND Columns2.object_id = table2.object_id