×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

you can use Visual Studio to create a Integration Services project under Business Intelligence Projects types. Search "SSIS hands on lab" or "SSIS tutorial". You will find some examples

Export SQL Server data to Excel: http://msdn.microsoft.com/en-ca/library/cc952922.aspx

Other SSIS how to videos:
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / I am using SQL Server Management Studio to generate dataset. Then I copy it to Excel. Then I attach the Excel file to an email. I need to do this boring stuff everyday. Is there anyway I can combine these into a single button?
    • SSIS
      • Hello coolmao. Do you mind explain more. I do not have privilege to install anything.
        Currently I have SQL Server Management Studio, Visual Studio 2008 and SQL Server BI Development Studio. I would need to know some keywords about the how to do this. I would need these keywords to search for the details. Thanks a lot.
        • you can use Visual Studio to create a Integration Services project under Business Intelligence Projects types. Search "SSIS hands on lab" or "SSIS tutorial". You will find some examples
          Export SQL Server data to Excel: http://msdn.microsoft.com/en-ca/library/cc952922.aspx

          Other SSIS how to videos:
    • 到一个BTN的时候, 就不用你来按这个BTN了.
      • Haha. That is right. I want to make it fully automation.
        • LS的意思是说,你这个职位就不需要了。你可以准备走人了。
          • My manager does not know techniques at all. I would tell him it is complete automation.
    • Business Objects
      • What is that? Never heard that before.
    • LS说的SSIS是最自动化的方法。如果你不懂那个,或者没有条件用SSIS,也可以选择半自动方式。在你的Excel里可以设置Data Connection。每天你只要打开你的Excel,点一下Refresh button,然后在Send As Attachment。
      • Thank you very much. I would like to do this first before I have learnt SSIS.
      • Having a problem: it appears I can only connect to table. Actually, on server side I have a query which needs to run everyday. Do you know how to link to query?
        • 估计你没找对地方。
          The Excel source provides four different data access modes for extracting data:

          * A table or view.
          * A table or view specified in a variable.
          * The results of an SQL statement. The query can be a parameterized query.
          * The results of an SQL statement stored in a variable.
          • You are right. Now I found where to link to the view in SQL Server, as well as create a VBA button to open email sending page.
            However, I am unable to automatically compose the email. Can you help, please?

            Next task for me is to schedule daily automation.
            • 没试过Excel能不能选Email Template。看来你要求越来越多,建议你还是看看SSIS吧。非要用土炮发射导弹也不是不可以,不过难度可大多了。
              • 不用SSIS(估计你没有SSIS环境) 也能编一个小EXE来完成你的这一小小要求, 然后你就天天喝茶RELAX.....
                1. 不管你用老的VB, 还是新的.NET (C# , VB.NET), 你需要编个EXE . 不会有问题吧? (老的象VB6.0, 选EXE PROJECT, CODING... COMPILED; 新的VS.NET 什么的选CREATE PROJECT, C# , VB.NET你自便, BUILD后去BIN FOLDER你能找到EXE)
                2. 在你的EXE 中用老的ADO 或新的ADO.NET 从DATABASE取回老的RECORDSET/新的DATASET 你应该会吧?
                3. 把RECORDSET/DATASET 写在EXCEL文件中你会吧?
                4. 用老的CDO/新的NET.MAIL NAME SPACE组建你的EMAIL, ATTACH EXCEL文件.
                5. 在SCHEDULE TASK中ADD TASK, CONFIGURE LIKE 5:00pm DAILY...ETC, 把上面的EXE定在这个TASK中.
                6. 天天休息喝茶了....
                • Thanks.
                  1. 不管你用老的VB, 还是新的.NET (C# , VB.NET), 你需要编个EXE . 不会有问题吧? (老的象VB6.0, 选EXE PROJECT, CODING... COMPILED; 新的VS.NET 什么的选CREATE PROJECT, C# , VB.NET你自便, BUILD后去BIN FOLDER你能找到EXE)
                  No. I do have problem to write a exe file. Any particular language do I need to know?

                  2. 在你的EXE 中用老的ADO 或新的ADO.NET 从DATABASE取回老的RECORDSET/新的DATASET 你应该会吧?
                  No. I do not know.

                  3. 把RECORDSET/DATASET 写在EXCEL文件中你会吧?
                  Yes, I done not through code.

                  4. 用老的CDO/新的NET.MAIL NAME SPACE组建你的EMAIL, ATTACH EXCEL文件.
                  No. I do not know.

                  5. 在SCHEDULE TASK中ADD TASK, CONFIGURE LIKE 5:00pm DAILY...ETC, 把上面的EXE定在这个TASK中.
                  Do you mean the schedule task in Windows or in some other system?

                  6. 天天休息喝茶了....
                  ...It seems this approach having too much work to do, especially learning...
                  • The easiest way is that you pay and ID does it for you. Then you can just drink tea and relax each day. Small money gives you big benefit, Right?!
            • Done with one click to send email, with some kind of warning.
    • 用SSRS吧,简单,5分钟搞定
      • I think that is the best approach to go. Let me learn something first...
    • 你button不用点, email也不用自己发, 用sql server job 运行xp_sendmail, 这个procedure是支持query的结果作为attachment的。
      • I do not think I have access to SQL Server Job. At least not in my desktop.
    • It should be a SSIS package + a SQL schduled job ( to run the SSIS package on daily basis).
    • 上面的方法大部分可行,但估计LZ都没权限