SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

Archive for January, 2013

SSIS – Error: Could not get a list of SSIS packages from the project

Posted by Prashant on January 28, 2013


While working on a ssis project recently I was trying to build the solution however it was failing again and again without showing any error in Error List !!! and when I checked the Output List it was saying the below message (refer Fig-1).

Error: Could not get a list of SSIS packages from the project.
===== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped =====

SSIS-Error-CantGetPackageList

Fig-1

This message says there is some missing objects in the project (.dtproj), as CreateDeploymentUtility property was set to True for this project the ssis was not able to show the actual error message.

So set the CreateDeploymentUtility to False and build the project again to get the actual error. In my case there was a package added in the project however because of some source control issues the .dtsx file was not available in the project directory.

Now that you got the error to fix; go ahead and fix the issue then set CreateDeploymentUtility property to True and build the project successfully.

Posted in SSIS | Tagged: , | 1 Comment »

SSIS – Create New Excel File Dynamically to Export Data

Posted by Prashant on January 12, 2013


Recently I got a requirement to export error data to a newly created excel file every time the SSIS package runs. The challenge here was to create new excel file with time stamp every time the package ran and export data to that newly created file.

In this post will demonstrate this step by step.

  • Create a template file to export the data. You need this template file to set up the package for the first time. Once the setup is done you can delete the template.
  • Create connection managers:
    • Add Excel Connection Manager pointing to the template excel file created before.
    • Add OLEDB Connection Manager pointing to the desired database (here I am using AdventureWorks2008R2 database)
  • Add an Execute SQL Task and use the below script as SQL Statement as shown in fig-2:
CREATE TABLE Data (`DepartmentID` LongText, `Name` LongText, `GroupName` LongText)

Fig-1
Fig-1

ExportExcel-Package11
Fig-2

  • Now setup a Data Flow Task to export the data.
    • Add a Data Flow Task to the package. Open the Data Flow Task, inside Data Flow tab add a OLEDB Source, select SQL Command as data access mode and use your query to fetch the required data. I will use the below query:
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
  • Add an Excel Destination to the package. In the excel destination editor select Table or View as Data Access Mode, select the name of excel sheet and do the required mapping. Set ValidateExternalMetadata property to False.

ExportExcel-Package12

Fig-3

  • Now go to Expressions property of Excel Connection Manager and set ExcelFilePath with below expression:
"S:\\ETL Lab\\CreateNewExcel\\ExportData_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"

and the package is ready to run.

You can download the sample package here.

Posted in Excel, SQL Server, SSIS | Tagged: , , , , | 15 Comments »

 
%d bloggers like this: