August 28, 2008 at 9:32 am
I am trying to create SSIS package that incoporates a SQL query which is a bunch of select statements. The output of this needs to be exported to an Excel spreadsheet and this file needs to be emailed to a specific client. I know how to do it via DTS but this server/DB does not have a SQL 2000 instance. I dont know what is the best and easy procedure to do this. I started out with creating a new SSIS package in BIDS and included three steps in my Data Flow task.
1.OLE DB Source : I specified the srvr, DB name and the select query here
2.Data conversion task :Here I selected the columns from the table that are used in the script.
3.Excel Destination task: I specified the location and name of the excel destination but it gives the error as 'column name cannot convert betweeb unicode and non unicode string data types'
The script is something like
select f_name + ' ' + l_name as 'Agent Name', right(user_Id, len(user_Id) - charindex('~', user_Id)) as 'User ID', PIN, (select org_name from tblPry where prty_id = 10773) as 'Organization Name' from party where prty_id in (select obj_py_id from tblPry_asn where sbj_py_id in (10773)) and datediff(dd, createtimestamp, getDate()) < 7
union
the columns have the data types as
tblPry
f_name varchar(32)
l_name varchar(32)
user_Id varchar(50)
Pin_Id int
org_name varchar(255)
party_name int (Primary Key)
table2
obj_py_id int (PK)
sbj_py_id int (PK)
Let me know what tasks are best to include for I have to schedule this as a job and mail the ouptput file.
Thanks in advance!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
August 28, 2008 at 4:53 pm
Hi
You can use import and export wizard for this purpose from management studio
August 28, 2008 at 6:51 pm
Import export wizard will not serve my purpose of mailing the excel spreadsheet on a sceduled basis that too when I have a script that does the select for me.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
August 29, 2008 at 5:45 am
Hi ,
You only need to convert the varchar fields in my opinion. Then mapp the copy fileds to the destination fileds in the data cnversion - mappings box.
Another thing you can do is right click on the destination excel spreadsheet and select show advanced editor.
In Output External columns click on each field that is a varchar type and select DT_STR for data type .Click OK and try runninig the SSIS package again.
September 4, 2008 at 6:03 pm
Advanced editor changes are not saved.Instead I tried doing the source as unicode but there was no output data, just the column names...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 9, 2008 at 1:49 am
Hi,
Add a data conversion transformation between the source and destination, open it and select the varchar columns you want. A row to edit the column will appear. From this chose the output datatype you want (in this case Unicode DT_WSTR). Important easily missed - the data is output to a new column with a default name in the Output Alias of You can click on it to edit it (Make sure its a recognisable name and don't reuse the same column name!). When you set the destination columns you now need to map the additional converted column to the output column not the one from the data ource. In the destination edit box you will see two adjacent columns listing the Input columns and the output column this is mapped to. The default will be the original source column. You need to click on the column name in the input column list. This is actually a select box that will allow you to seek available columns. Change the column from the original source column name to the new column created for it in the Data Conversion Transformation.
The process is not intuitative. The transform doesn't just create an alias for the existing column but uses the alias as the name for a new column that is added onto the dataflow with the original left in it unchanged.
Hope this helps - I certainly wasted a bit of time before it clicked and it wasn't clear from any books I read either!
September 21, 2008 at 4:56 pm
Thanks a bunch Williams...it really works..
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply