August 23, 2012 at 3:57 pm
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?
August 23, 2012 at 9:51 pm
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.
August 24, 2012 at 10:16 am
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
August 24, 2012 at 11:51 pm
(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.
August 28, 2013 at 11:16 am
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;
----
August 28, 2013 at 11:22 am
Gah. you'll want this in the first for loop:
foreach (DataRow t in ...) {
sql.Clear();
...
August 28, 2013 at 11:32 am
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...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply