Just another WordPress.com site

MS SSIS Tips

Leading Zero missing when export into flat file

In SSIS, when export a decimal field into a text flat file, if the value before the decimal place is 0, it will not show up in the flat file. eg: value 0.89 will show up as .89, -0.32 as -.32, 0.00 as .00. How can we get the leading zero back?

Solution:

Add a Derived Column Task, set the expression as

Price < 1 && Price > -1 ? "0" + (DT_WSTR,10)Price : (DT_WSTR,10)Price
Advertisements

How to create a 2012SSIS project in a solution

  1. Open a new SSIS project in the solution as in SQL 2008

  2. In the Solution Explorer panel, add the connections under Connection Managers on the project level.

  3. Create Project parameters like Source and destination database or file path.ProjPara

  4. After the development is done, deploy the solution to the database server.

  5. In the Management Studio, there’s new project will be added under the “Integration Services Catalogs”

  6. Manually create the Environment folder under the “Environments “, in Variables screen, add the variables same as in the Project Parameter.

  7. Right click on the project under the “Projects”, choose Config, in the Reference page, add a new environment, select the one we just created in last step. Then in the Parameters page, click on the “…” option, chose “Use environment variable” and select the mapping parameters in the Environment.

  8. Create a new job, calling the new SSIS Package, under the Configuration page, make sure the Environment checkbox is checked, and the Parameters are all mapped to the environment variables.

  9. Execute the job to test.


SSIS OLE DB Source Returns No Rows

Issue:

Sometimes when we build an SSIS package with a data flow task of moving data from a “OLE DB Source”, you can preview the query result with no issues in the “OLE DB Source Editor”, but when execute the package or Data flow task, there’re 0 rows got transferred.

Cause:

The execution information of the statement is sent out to the client.

Solution:

Add the “SET NOCOUNT ON” at the beginning of the SQL statement.


How to uninstall SSDT BI for VS 2012

How to uninstall SSDT BI for VS 2012

In Control Panel, Programs, click on “Uninstall a program”, from the “Uninstall or change a program” list, uninstall below items:

  • Microsoft Visual Studio 2012 Shell (multiple options)

  • Microsoft Visual Studio Tools for Application 2012

  • SQL Server 2012


How to call a SQL Package job in 32-bit mode?

  1. In VS open the package, from Project->Property, in Debugging page, set the Run64Runtime to “False”.

  2. In SQL job properties window, double click the Steps item to open the Job Step Properties window, in the “Execution Option” page, check the “Use 32 bit runtime” option.


How to add header and footer lines to a text extract with different format in SSIS

The idea is to use the windows command in “Execute Process Task” to

  • Append the staging data file to the target file with Headers.

  • Append the Footer data to the target file.

Add a new line in the Header

Header_FooterEdit

The argument used in Task “CombineHeader” is:

/c Type DataFile.txt >> TargetFile.txt

Add a new line in the Footer

1. With a carriage return CR CL

In the Argument, the final script should be:

/c echo Footer 123 >> I:\SSIS\Test.txt

Note: The 123 is the count of the records, and my client wants a Tab instead of a space in between the Footer and the count.

2. With a carriage return CR CL

In the Argument, the final script should be:

/c echo|set /p x= Footer 123 >> I:\SSIS\Test.txt

Note: echo|set /p dummieVariable= is the command which will append a new line at the end of the text file without the Carriage Return(CR CL).

So I use the expression as:

"/c echo|set /p x= Footer    " + (DT_WSTR, 6)@[User::RCNT]+" >> \""+ @[User::FilePath]+"\""

How to Fix “Connection is busy with results for another command” in SSIS

When this error raises, it is always when multiple data flow run in parallel.

  1. Check if the RetainSameConnection property on the connection manager has been set to True, if yes, check it to “False”

  2. Take off the Table lock from the destination.

In Data Flow Tasks, if there is OLE DB Destination, double click it, you can see a CheckBox named “Table Lock”. Un-check it.

If there is SQL Server Destination, double click it, go to “Advanced” panel, you can also see a CheckBox named “Table Lock”. Un-check it.