SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


generate dynamic column names from a table using ssis


generate dynamic column names from a table using ssis

Author
Message
efioj
efioj
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 36
I tried to create Execute SQL task for the following procedure by creating
1. variable as ColumnNames
2. assigned to expression
There is also a variable to get the table name from a RecordSet destination

DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='tablename'

SELECT @c = @c + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @t
ORDER BY colid
SELECT Substring(@c, 1, Datalength(@c) - 2)

The problem is how to assign this code to expression since variables @c and @t consider as ssis varibales which not defines but that are sql variables

Once I get the list of the columns, I will use them in insert sql statement
insert into tablename(clomunnames) select * from db.tablename

Would you please help me in this regard?
n79799
n79799
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 122
You can assign the result set of your execute sql to a variable. Look under resultset in the ExecuteSQLTask. Map the resultset to a variable of Object type. Then in a script task, get the info out of that variable.

Hope this helps.
efioj
efioj
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 36
let explain myself

I want to incorporate the following code in ssis and put the result into RecordSet. Tablename comes from another RecordSet.

I tried using Execute T-SQL Statement Task and got an error

SET @[User::c] = ''
SET @[User::t]=@[User::TableName]
SELECT @c = @c + c.name + ', '
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @[User::t]
ORDER BY colid
SELECT Substring(@[User::c], 1, Datalength@[User::c]) - 2)

error message

Execute SQL Task] Error: Executing the query "
SET @[User::c] = ''
SET @[User::t]=@[User::Tabl..." failed with the following error: "Incorrect syntax near 'User::c'.
Must declare the scalar variable "@c".
Must declare the scalar variable "@".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

OR

I am trying to insert data from Source DB to destination DB. Here table name should be dynamic.

Insert Into @DTableName
(@Columns)
values

Select * from @STABLENAME

In Query detination columns names we should provide. So I have taken @columns variable to return column names with cama seperation

Declare @Columns varchar(max)

SET @Columns =(select SUBSTRING(
(SELECT ',' + s.name
FROM (select name from sys.columns
where object_id=(select object_id from sys.tables where type='u' and name =@DTableName
)) s
FOR XML PATH('')),2,200000) )

While I parse this expression in Execute SQL SQL Source Staatement expression editor I am getting this Error:

[Execute SQL Task] Error: Executing the query "insert into dbo.Test(@columns )selec..." failed with the following error: "Must declare the scalar variable "@column".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I get any help from either request, then that would be great
n79799
n79799
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 122
(Well first....the company Konesans has something similar prebuilt in the form of a custom task. You might want to have a look at that)

Second....your most recent example is missing a declare statemnt....hence the error.

Then.....even if you had a declare statement.....in sql you can't say
insert into tablename
(@list_cols)
values('a','b') etc.
The above wouldn't work. Another similar example,
in sql you can't do :
select * from tablename where emp_name in (@list_names). This wouldn't work.
The correct way to do this is by using dynamic sql. ie build your query as a string in sql and then use exec()
or maybe execute_sql(please look up the help on this).
so basically, in your example, you can do :
declare @cols varchar(max)
declar @sql varchar(max)
declare @tblname varchar(50)
declare @insert_vals varchar(max)
set @cols = 'fname, lname, age, sex, empid)' etc
set @insert_vals = 'john, doe, 30,115)' etc
set @sql = 'insert into + @tblname + '('
set @sql = @sql + @cols + ')'
set @sql = @sql + ' values(' + @insert_vals + ')'
Now, your @sql contains the whole sql. Now you just say :
exec(@sql) ---> This will execute your sql string like a sql statement.

ok, so with that sorted out, your next problem at hand :
dynamically inserting values into table (ie table being decide at runtime) :

Please rethink about questions like :
are the cols in src the same as the corresponding dest table in each case
It looks as if the tbl name are also the same....if not then its not a simple insert stmnt....you would be applying some logic.... like for dest tbl1, src colA goes to srccolb etc
is it a one to one mapping / transform....no derived col, no logic etc.
What about the datatypes on each side(do they exactly match etc)

I would attempt this in a a script task like so....
Get the src cols dynamically and put that in a variable
Get the src values dynamically and put that in a variable
Get the dest tblname in a var
Get the dest cols in a var
Form the sql as a var(as shown above) (keep in mind the datatypes, lengths, conversions, null value etc....not an easy task)
EXEC the sql.

Run this whole thing in a foreach loop (either ssis foreach loop or a c# foreach loop inside the script task)

I am hoping this would give you some clearer picture of the solution.
corey lawson
corey lawson
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 564
Here's one way, using ADO.Net + ODBC connection... (this is more or less from my working SSIS 2008/2010 ScriptTask C# code...)

The methodology is similar for ADO.Net + SqlConnection. SqlConnection also has a GetSchema() method.



var conn = new OdbcConnection();
conn = (OdbcConnection)Dts.Connections["connMgrName"].AcquireConnection(null);

var tbls = conn.GetSchema("Tables");
var tblCols = conn.GetSchema("Columns");
var sql = new StringBuilder();

foreach(DataRow t in tbls.Rows) {
sql.AppendFormat("create table {0}(", t["TABLE_NAME"]);
foreach(DataRow c in tblCols.Rows) {
//the actual field names returned by GetSchema("COLUMNS") can change depending on the source ODBC driver...
//grok conn.GetSchema("DataTypes") to see what the specific driver returns for data types...
//for some ADO.Net drivers, there are XML docs here that show the mappings used by SSIS:
// C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles
// I don't know if other 3rd-party drivers do or not...
var dataType = c["TYPE_NAME"].ToString().Trim();
var colName = c["COLUMN_NAME"].Value;
var colLength = (int)c["COLUMN_SIZE"].Value;
var colRadix = c["NUM_PREC_RADIX"];
var colDigits = c["DECIMAL_DIGITS"];

//this is naieve code below, assuming 1:1 mapping between source and destination data type names
//and nothing fancy, like varchar(n) vs varchar(max) for SQL Server...
//To see all the different metadata collections returned by a given ODBC Datasource,
//grok the output from GetSchema("MetaDataCollections").
switch(dataType) {
case "CHAR":case "VARCHAR":
sql.AppendFormat("{0} {1}({2}),", colName, dataType, colLength);
default: //Integer, SmallInt, DateTime, etc...
sql.AppendFormat("{0} {1},", colName, dataType);
}
}
sql.Append(")");
//need to fix the last line or so...
sql.Replace( ",)", ")" );
}

MessageBox.Show(sql.ToString(), "Create table DDL...");

//return the sql string to a package variable so it can be used later in an ExecuteSQL task
Dts.Variables["User::pkgCreateTableSql"].Value = sql.ToString();

Dts.TaskResult = (int)ScriptResults.Success;


----
corey lawson
corey lawson
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 564
Gah. you'll want this in the first for loop:

foreach (DataRow t in ...) {
sql.Clear();
...


corey lawson
corey lawson
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 564
Another gah!!! You'll need to grab the columns for a specific table... doh!


foreach(DataRow t in tbls.Rows) {
sql.Clear();
sql.AppendFormat("create table {0}(", t["TABLE_NAME"]);

var tblCols = cols.Select("TABLE_NAME = '" + t["TABLE_NAME"]+ "'");

foreach(DataRow c in tblCols.Rows) {
....



In my pkg, I grab the GetSchema("Tables") & GetSchema("Columns") data sets into pkg variables in a separate task. Since I'm using these multiple times, it makes sense to me to grab them only once, as they can be expensive...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search