A lot of people asked me if there is a way we can just use same package to generate csv files to pull from different tables, to achieve that we have to do it in two steps as shown below.
Step1:
Create a stored procedure
Query:
CREATE procedure [dbo].[usp__UniversalCSV_SQLGenerator]
@sourceTable VARCHAR(100)
,@CSV_SqlQuery varchar(max) output
as
begin
DECLARE @keyColumn VARCHAR(100) = @sourceTable+'_ID' --this can be set to the primary key of the table if you donot want to show
Declare @MaxColumnID INT
Declare @SQLQuery varchar(max) =''
Declare @SQLQueryOrderby varchar(max) =''
Declare @DummyColumns varchar(max) = 'col1,col2,col3' --these columns are removed from generated sql query,this can be made input parameter
set @MaxColumnID=(select count(colid) FROM syscolumns c join sys.objects o
on o.object_id=c.id
where OBJECT_NAME(id) = @sourceTable and o.schema_id = schema_id('dbo') --change the schema name here if need make an input parameter
AND c.name not in (''''+REPLACE(@DummyColumns, ',') , ',',''',''')+'''')
and c.name <>@keyColumn)
DECLARE MY_CURSOR Cursor
for
SELECT
CASE WHEN ROW_NUMBER() OVER (ORDER BY column_id) = 1 THEN 'Select cast(ISNULL(cast(' ELSE '+'+''','''+'+'+'ISNULL('+' cast(' END +'[' +c.name + ']'+' as '
+ CASE
WHEN c.system_type_id in (167,175)
THEN UPPER(t.name)+'(' +
CASE
WHEN c.max_length = -1
THEN 'MAX'
ELSE CONVERT(VARCHAR, c.max_length)
END + ')),'''')'
WHEN c.system_type_id in (36,231,239)
THEN 'VARCHAR(' +
CASE
WHEN c.max_length = -1
THEN 'MAX'
ELSE CONVERT(VARCHAR, c.max_length) END + ')),'''')'
ELSE 'VARCHAR(' +
CASE
WHEN c.max_length = -1
THEN 'MAX'
ELSE CONVERT(VARCHAR, c.precision+c.scale) END + ')),'''')'
END
+CASE
when (ROW_NUMBER() OVER (ORDER BY column_id))=@MaxColumnID
then ' as varchar(max)) as SQL_QUERY,OUTPUT_FILE_TYPE from dbo.'+@sourceTable+' where EXCEPTION_REASON is null'
else ''
end
,CASE WHEN ROW_NUMBER() OVER (ORDER BY column_id) = 1 THEN ' Order by '+'[' +c.name + ']' ELSE ',' +'[' +c.name + ']' end
FROM sys.columns c
JOIN sys.types t
ON c.system_type_id = t.system_type_id
join sys.objects o
on o.object_id=c.object_id
WHERE OBJECT_NAME(c.object_id) = @sourceTable
and o.schema_id = schema_id('dbo')
AND c.name not in (''''+REPLACE(@DummyColumns, ',') , ',',''',''')+'''')
and c.name <>@keyColumn
order by column_id
Open MY_CURSOR
DECLARE @SQLQueryLine varchar(max)
DECLARE @SQLQueryOrderbyLine varchar(max)
Fetch NEXT FROM MY_Cursor INTO @SQLQueryLine,@SQLQueryOrderbyLine
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
set @SQLQuery=@SQLQuery+@SQLQueryLine
set @SQLQueryOrderby=@SQLQueryOrderby+@SQLQueryOrderbyLine
FETCH NEXT FROM MY_CURSOR INTO @SQLQueryLine,@SQLQueryOrderbyLine
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
select @CSV_SqlQuery= @SQLQuery+@SQLQueryOrderby
--select @SQLQuery+@SQLQueryOrderby
end
Step 2:
1) Create a new package
2) Create variable "varCSVSelectQuery" and "varTableName" both string data type
3) Drag and drop "execute sql Task" into the control flow and call the stored procedure as shown below
4) Drag and drop "dataflow task" and connect the output "green line" of the "execute sql Task" to the dataflow so that dataflow runs only after execute sql task.
Inside the dataflow
· Drag and drop oledb source and inside the oledb source use variable "varCSVSelectQuery" as source
· Drag and drop flat file destination and then select new > delimited > browse to some location then click on columns and then ok and you should see the setup as shown below.
· Run the package and it should create a csv file for you, csv file name can be made dynamic by setting connection string expression of the flat file destination in the connection manager
Add-on/Enhancement:
To make it more robust we can create a table which has table_name column, dummy_columns column and inside the SSIS package have a "execute sql" task to pull records from the table and "foreach loop" task to loop through the records and create csv files.