Generate sql using SSIS

[manmaria]

I have a task to generate some sql using some standard statments and some variable names as table name. For example

CREATE PROCEDURE DBA.tmpBSPOt ( )
BEGIN
IF ( SELECT STRING(Value) FROM @tablename
WHERE Name = 'ImplementBSPOT' ) = '1'
----------------------------------------------------------------------------------------------------------------------
-- Table: @tablename - Insert and Delete triggers for audit log capture.
----------------------------------------------------------------------------------------------------------------------

Something like that. How do I do that. I was thinking to put the whole statment until the parameter(@tablename) in to

SELECT 'THE STATMENT' then I want to take the input parameter, but I am not successful yet. How do you append the text to the same file?

TIA

 

[MDXer]

this is possible but my first question is why are you using SSIS to create stored procedures? It would be much easier to do it in T-SQL.

 

[manmaria]

Only reason I am using SSIS is to append the text to a file. What I am doing is inserting the standard script in to a text and using the table name as a parameter and getting the columns information from the system tables. This is a template sql for moving data from base to staging tables. So instead of every programmer to write their own coding, I am creating a template, so when they run this it generates a script with the table name.

Is that makes any sense? If you have some other ideas, please share.

TIA

 

[MDXer]

I do something like this in the follwoing method.

1) Table

TableName   QueryName   BaseQuery
MyTable     MyQuery     Select Col1,Col2 From MyTable

2) I then have a package variable SQLStatement

3) SQL Task that executes query to Return needed SQL which is then used to populate to populate the variable.

4) Data Source I use a SQL Command from variable.


if you are moving whole table sets this is fine but if a package needs only specific columns from a table then this method can cause problems as you don't always need all columns.