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 + ' 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy