SSIS Package to create a dynamic csv file

Question

Post reply

SSIS Package to create a dynamic csv file

agnetha_11

Mr or Mrs. 500

Points: 590
More actions
January 17, 2013 at 12:32 pm

#287814

Hi,

I am creating a SSIS package

First I, executes a stored procedure which creates a table in sql with data. Then I use a script task with three variables

1. Directory this is the path on a network drive eg \\Server01\Test

2. FileName is SSIS_TEST

3. OutputFile = \\Server01\Test\SSIS_TEST.csv

In the edit screen of Script Task, I have selected (Directory and FileName for Readonly Variable. Output file is the WriteVariable)

Script VB, I found on a blog for adding month&year to the file name

Public Sub Main()

'

' MSSQLTips: Modify Output File Location

'

Dim sFileNameAndPath As String

Dim sDirectory As String

Dim sFilePrefix As String

sDirectory = Dts.Variables("Directory").Value.ToString

If Not (sDirectory.EndsWith("\")) Then

sDirectory = sDirectory + "\"

End If

sFilePrefix = Dts.Variables("FileName").Value.ToString

sFileNameAndPath = sDirectory + sFilePrefix + "_" + Month(Now.Date).ToString + Year(Now.Date).ToString + ".csv"

Dts.Variables("OutputFile").Value = sFileNameAndPath

Dts.TaskResult = ScriptResults.Success

End Sub

I have a Data Flow Task --- Ole db Source and a Flat File Destination (Flat File Manager expression is '@OutputFile)

When I execute the package, I get the below error,

==================================

Package Validation Error (Package Validation Error)

===================================

Error at Block_Vision_Eye_Exam_FTP: The connection "{0C8FF18D-239C-4683-AC43-DA0BFE80E32A}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Data Flow Task [SSIS.Pipeline]: Cannot find the connection manager with ID "{0C8FF18D-239C-4683-AC43-DA0BFE80E32A}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "runtime connection "OleDbConnection" (24)" in the connection manager collection of "component "OLE DB Source 1" (14)". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

Error at Data Flow Task [SSIS.Pipeline]: component "OLE DB Source 1" (14) failed validation and returned error code 0xC004800B.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------

Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Please advice.