Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

generate dynamic column names from a table using ssis Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 3:24 PM
Points: 6, 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?
Post #1349381
Posted Thursday, August 23, 2012 9:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:04 PM
Points: 35, Visits: 98
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.
Post #1349435
Posted Friday, August 24, 2012 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 3:24 PM
Points: 6, 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

Post #1349799
Posted Friday, August 24, 2012 11:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:04 PM
Points: 35, Visits: 98
(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.









Post #1350010
Posted Wednesday, August 28, 2013 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:18 PM
Points: 156, Visits: 367
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;

----

Post #1489342
Posted Wednesday, August 28, 2013 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:18 PM
Points: 156, Visits: 367
Gah. you'll want this in the first for loop:
foreach (DataRow t in ...) {
sql.Clear();
...

Post #1489346
Posted Wednesday, August 28, 2013 11:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:18 PM
Points: 156, Visits: 367
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...
Post #1489353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse