Getting An ASP .Net VB Script To Work In SSIS As Script A Task

Mar 26, 2008

I have designed a VB Script in ASP .NET (as that is what I'm used to using) that will look at a specific file at a specific path and send the Last Modified information to a database. Now I would like to move this script to the Script a Task function in SSIS so that I can check before running the rest of the package that the file I want to pull the data from has actually been updated. The ASP .NET code (which does work) is as follows (connection string removed):

<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim objCONN As New OleDbConnection("REMOVED")
Dim objSQL As New OleDbCommand()
Dim strInsert As String
Dim objFilesSystem As Object
Dim objFile As Object
Dim strDate As String
objFilesSystem = CreateObject("Scripting.FileSystemObject")
objFile = objFilesSystem.GetFile("C:Flat_File_Data/Force_Receipt_To_Clear.xls")
strDate = objFile.DateLastModified
strInsert = "1, cast( '" & strDate & "' as datetime ) ) "
objCONN.Open()
objSQL.Connection = objCONN
objSQL.CommandText = "INSERT INTO CTS.dbo.IS_THERE_CHECK VALUES( getdate(), " & strInsert
objSQL.ExecuteScalar()
objCONN.Close()
%>

What follows is as close as I could get in SSIS (I had to set Option Strict to Off). It produces no error, at least not as far as I can tell, nor does it write to the database when run.

Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim objCONN As New OleDb.OleDbConnection("REMOVED")
Dim objSQL As New OleDb.OleDbCommand()
Dim strInsert As String
Dim objFilesSystem As Object
Dim objFile As Object
Dim strDate As String
objFilesSystem = CreateObject("Scripting.FileSystemObject")
objFile = objFilesSystem.GetFile("C:Flat_File_Data/Force_Receipt_To_Clear.xls")
strDate = objFile.DateLastModified
strInsert = "1, cast( '" & strDate & "' as datetime ) ) "
objCONN.Open()
objSQL.Connection = objCONN
objSQL.CommandText = "INSERT INTO CTS.dbo.IS_THERE_CHECK VALUES( getdate(), " & strInsert
objSQL.ExecuteScalar()
objCONN.Close()
Dts.TaskResult = Dts.Results.Success
End Sub

View 5 Replies


ADVERTISEMENT

How Do You Get The FTP Task To Work??

May 22, 2008



I have been trying to get a simple FTP task to work but I seem to be missing something.

I set up the FTP Connection with the FTP Address, username, password and everything. Then I set up the FTP task to send a file and it doesn't want to work. I know that it has something to do with saving the password but I can't seem to figure out how to get it to work.

So, what is the secret to getting this to work??

View 22 Replies View Related

Help, Ftp Task Does Not Work

Jul 25, 2007

Hi,



I have successfully used the ftp task for all my packages, and have never had a problem before.



However, when attempting to download files from a new ftp site today, I am getting a "directory not found" error.



My ftp connection manager is set up correctly, and when I test the connection, it succeeds.



I am also able to ftp to the site using Windows Explorer, and view all the files in the outgoing folder.



My remote path is set up as: /outgoing/*.*



This is the error I'm receiving:



[Connection manager "FTP Connection Manager"] Error: Changing current directory to "outgoing". 550 /outgoing: folder not found .



I really don't understand what the problem is here!



Thanks





View 5 Replies View Related

SSIS Task Transfer SQL Server Objects Task And Default Constraints On Tables

Feb 21, 2008



I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.

The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.

I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)

Any option anyone knows will help.

Thanks.

View 17 Replies View Related

SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction

Feb 1, 2007

In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View 9 Replies View Related

New 2005 SSIS Task: File Properties Task

Nov 7, 2007

A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.

http://www.pragmaticworks.com/filepropertiestask.htm

View 5 Replies View Related

Valid Query Wont Work As DTS Task???

Jan 23, 2004

i have a query which works fine when i run it from Query Analyzer.

when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:

ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL

It throws and error that says:

"Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.

View 7 Replies View Related

Scheduling Simple Cdosys Email Task Does Not Work

Feb 26, 2007

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis.  The email is sent using an activex script in a "SQL 2000 DTS Package Task".  When executed manually, the email is sent ok.   When scheduled (and run under our SQL agent account), it fails.  Can anyone point me in the right direction?  Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
    dim mailer
    set mailer = CreateObject("CDO.Message")
    dim cdoconfig
    const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
    const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
    set cdoconfig = CreateObject("CDO.Configuration")

    with mailer
        set .Configuration = cdoconfig
        .BodyPart.charset = "unicode-1-1-utf-8"
        .BodyPart.ContentTransferEncoding = "quoted-printable"
        .Fields("urn:schemas:httpmail:importance").Value = 2
        .Fields.Update 

        .Subject      = "Notification"
        .From         = "donotreply@test.com"
        .TextBody   = "TEST"
        .Bcc           = "someone@test.com"
        .Send
    end with
'-------------


Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

View 2 Replies View Related

Transfer SQL Server Objects Task Doesn't Work

Apr 24, 2006

Source Database: SQL Server 2000

Destination Database: SQL Server 2005

No matter what table, view, or stored proc I pick, it always says that it doesn't exist at the source. I know it exists because I am picking it from the list of tables, etc. that the GUI provides.

Any suggestions?

Jonathan Allen

View 1 Replies View Related

How Do You Get A Reference To The Task Host In An SSIS Task

Feb 26, 2008

I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1

According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?


I need to get a reference to the task host in an SSIS Task component.

Basically the scenario is this:

I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.

What I did is this:

I've implemented IDtsTaskUI and during the initialize section I wrote:

Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
' Store the TaskHost of the task.
Me.taskHostValue = taskHost
Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
myTask.myTaskHost = taskHost
End Sub

My Task is named: CustomTask. I have a public variable in my task as follows:

Public NotInheritable Class CustomTask
Inherits Task
Implements IDTSComponentPersist
Public myTaskHost As TaskHost = Nothing

Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.

Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.

I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI

How do I solve this?



View 10 Replies View Related

Transfer SQL Server Objects Task Doesn't Work With Schemas

May 31, 2006

The task has a fault if you try to copy a table with a schema like [test].[table1]

The error says

Error: 0xC002F363 at Flyt til DM, Transfer SQL Server Objects Task: Table "table1" does not exist at the source.

Even though i just selected it from the table list minutes before.

View 7 Replies View Related

Sql Server 2005 Maintenance Task Delete Files Does Not Work

Mar 24, 2008

I used the toolbox to select maintenance cleanup task to create the job to do this. In reading similar notes regarding this problem, some people mentioned that there was a choice to include subfolders. I do not have this choice. When I execute select @@version I get Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) . This is running on a cluster. Any idea what is going on here? Thanks.

View 6 Replies View Related

Sending An HTML Mail Message With The Script Task DOESNT WORK 4 ME

Oct 3, 2006

I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail

Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)

Dts.TaskResult = Dts.Results.Success

End Sub

Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)

Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml

mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub

View 3 Replies View Related

Why Does Bulk Insert Task Not Work From Remote Servers With Windows Authentication?

Jun 10, 2007

Using Bulk Insert Task extensively in our solution. Everything was working great till we deployed it in stage columns. The database server is different from application servers. We have ASP.NET web services driving SSIS packages on application server. After struggling thru several security issues to get this working (ended up creating an application pool with a domain account) we are now stuck with this problem. On a different note still don't understand what specific security permission is available to domain account that makes it work.

Read in some blog that SQL Server 2005 SP2 Beta had this (Bulk Insert) fixed but not in final production version. Is there a specific reason why this is so?

SSIS and the API is quite easy to work with but associated security and deployment issues are not always clear. A lot of answers seem to be coming from end users - thanks a lot to all for sharing your experiences - sadly not presented clearly in SSIS documentation.

View 2 Replies View Related

Package With File System Task Doesn't Work Without Sensitive Data With User Keys

Dec 14, 2006

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.

View 2 Replies View Related

How To Work With Ssis...

Apr 3, 2007

How Do I work on SSIS? Where to start...........???

View 7 Replies View Related

How To Work With SSIS

May 8, 2008

Hi all.,
I am new to SSIS,if it is a wrong place to post this, please forgive me.
How to work with SSIS., I need to use SSIS in my project., Please can any one tell a link to learn how to work with SSIS.,

I am looking forward for a link.,

View 2 Replies View Related

Can SSIS Work In A Cluster

Apr 25, 2006

We have just installed a new SQL 2005 Clustered server and loaded integration services. However it appears that integrations services may not work properly in a clustered environment.

So can integration services be setup on a clustered SQL 2005 server?

View 4 Replies View Related

Samples From SSIS Book Do Not Work

Aug 23, 2007

the book "microsoft sql server 2005 integration services" by kirk haselden claims you can download examples and source code at www.samspublishing.com

er no

what happens is this

go to the site
look for the 'downloads' link
see there isn't one
search for authors
h etc.... lose a few minutes of your life
find the book

yay

it forces you to sign up for an account in order to get the samples - outrageous

prepare for endless spam from sams

sign up

click on download samples
find the configedit folder
load into vs
run it
it doesn't work!!


thanks

Warning 1 The referenced component 'SourceLibrary' could not be found.
Warning 2 The referenced component 'SourceGrid2' could not be found.
Error 3 Unable to find source file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigurationsEditorinDebugConfigurationsEditor.exe' for assembly 'ConfigurationsEditor.exe', located in '[TARGETDIR]' K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditSetupConfigEditSetupConfigEdit.vdproj SetupConfigEdit
Error 4 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 9 7 ConfigEdit
Error 5 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 10 7 ConfigEdit
Error 6 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 87 40 ConfigEdit
Error 7 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 11 15 ConfigEdit
Error 8 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 24 17 ConfigEdit
Error 9 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 25 11 ConfigEdit
Error 10 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.Designer.cs 318 17 ConfigEdit
Error 11 Metadata file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlinDebugConfigEdit.dll' could not be found ConfigurationsEditor
Warning 12 Could not resolve this reference. Could not locate the assembly "SourceGrid2". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit
Warning 13 Could not resolve this reference. Could not locate the assembly "SourceLibrary". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit


one could argue that ssis should have a config editor supplied with it, instead of having to go into notepad

View 1 Replies View Related

Can SSIS Work Without SQL SERVER 2005

Feb 20, 2006

Can I install only [Bus....integ.. dev.... st..]. on my machine without SQL 2005? I have a sybase database management system.. can I work with SSIS?

Can it extract something from IBM DB2/DATACOM/LOTUS NOTES/VSAM/???

 

pl give me answers with justifications!!!!

View 8 Replies View Related

Getting SSIS To Work With SQL Server 2000

Feb 1, 2008



Hi,

I am completely new to SSIS. Is there a way to get SSIS to work with a SQL server 2000 database. Is there a SQL 2000 Add in that can be utilized. Basically I want to save the SSIS package to the SQL 2000 database and schedule it to run.

Please let me know if you want any more details.

thanks in advance.

View 9 Replies View Related

Kerberos Delegation Don't Work With SSIS ?

Aug 10, 2007

Hello,

I have configured Kerberos delegation for several web services. One of the web service calls SSIS packages, but the packages don't run with the expected impersonate user : the package starts with the imporsonate user, but continue with ASPNET user (which is not allowed to execute SSIS and connect to DB).

If the web service is called directly (no delegation), SSIS packages run with the correct user. It looks like than there is an autenthicate issue, but kerberos is configured and web services can run from one to another with the impersonate user. The issue occured only when I call SSIS packages.

Here is a extract of the SSIS log file :



Code Snippet <dtslog>
<record>
<event>PackageStart</event>
<message>Beginning of package execution.
</message>
<computer>WKS-GE-BRAZILIA</computer>
<operator>WKS-GE-BRAZILIAPascal.Brun</operator>
<source>ImportMonthlyCSV</source>
<sourceid>{D053CB99-FDE4-492D-83BC-821E1B34704B}</sourceid>
<executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid>
<starttime>09.08.2007 17:31:02</starttime>
<endtime>09.08.2007 17:31:02</endtime>
<datacode>0</datacode>
<databytes>0x</databytes>
</record>
<record>
<event>OnError</event>
<message>SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Data Warehouse" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
</message>
<computer>WKS-GE-BRAZILIA</computer>
<operator>WKS-GE-BRAZILIAASPNET</operator>
<source>Import CSV</source>
<sourceid>{284D3166-F372-4B03-86C1-75A4D8DC9A5C}</sourceid>
<executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid>
<starttime>09.08.2007 17:31:02</starttime>
<endtime>09.08.2007 17:31:02</endtime>
<datacode>-1071611876</datacode>
<databytes>0x</databytes>
</record>
...





Any help is required.
Thanks in advance.

View 4 Replies View Related

How To Work With Link Server In SSIS

Apr 24, 2006

I have a query that joins with different table using link server in the database. SSSI doesn€™t take the query. How to make the SSIS package query to use the link server in the data base ?

thanks

View 2 Replies View Related

SSIS Setup For Team Work

Jun 22, 2006

I would like to setup SSIS project so that multiple developers can work on same project. I am having issues with protectionlevel properties while another developer opens the package created by other developer.

Can anyone guide me on setting up project so that multiple developers could open the package and run (not simaltaneously though). Also tips on setting up source safe or team foundation will be appreciated!

Thanks

Mahesh

View 3 Replies View Related

Oracle Query Does Not Work In SSIS

Nov 7, 2006

Hello All,

I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:

select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID

Here ARE the following errorS I get when running from SSIS:

[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".

[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.

Any help?

Regards,

Raju





View 3 Replies View Related

SSIS Error Output -- How Is It Supposed To Work?

May 29, 2006

I€™m importing a Flat file (delimited text) into a sql server database table and trying to trap any import errors in another output Flat file.

I create a Flat File Source task. After tweaking the task, the Source €œError Output€? tab shows all the input columns and all €œError€? and €œTruncation€? values are set to €œRedirect row€?

Next I add a €œSQL Server Destination€? task and connect the Source green arrow to it.

Finally I add a Flat File Destination task (error Output task), connect the Source red arrow to it, click €œOK€? in the €œConfigure Error Output€?, and finally add a connection manager to the Error output task. When I look at the Mappings tab of the Error Output file it shows only three available input columns: €œFlat File Error Output Column€?, ErrorCode, and ErrorColumn

I€™m not sure where they came from but that info is not very useful to me. I want to know which line the error occurred on as well as the bad column(s). If nothing else, I need to see at least the actual row that was bad. How can I get that information?



Barkingdog

View 8 Replies View Related

Precedence Constraint In SSIS Didn't Work

Aug 27, 2007

Dear all,
I've been searching the article for error handling in SSIS but seems no article have same problem exactly as mine. In my package there's container, it contains Data Flow task and some of Script tasks. I put an error precedence from Data Flow task into Script task that contains script for log an error that might be occured. The Data Flow task imports data from flat file into SQL Server 2005 and I've set a semicolon as the column delimiter. Unfortunately there is a data in flat file that had one column contain semicolon as its value.
This would trigger an error and I hope the error would be logged into a table as I wrote inside Script task. But it didn't work. The error precedence won't work, the package stop in flat file source instead. I've been trying the event handler but it didn't work either. Maybe I got wrong implementation, anybody can help me explain the error handler and solve the problem ?
Here is the capture of my package, since I didn't know how to attach the picture in this forum.
Thanks in advance.


Best regards,

Hery

View 12 Replies View Related

Can SSIS Work With An XML Web Service? (Verisign Payflow Pro)

Feb 20, 2007

I'm trying to figure out a solution for posting financial transactions against our Payflow Pro (Verisign) payment gateway (web service) using SSIS. The process I have in my mind goes like this...

1.) Select the appropriate records from our financial system DB.

2.) Iterate through each record and post the pertinent values against the payment gateway web service.

3.) Create log files for successful and failed transactions.

The log files would then be manually imported into our financial system.

Thanks in advance.

View 9 Replies View Related

SSIS Work Offline Switch In BIDS

Jun 5, 2007

If in BIDS I set "work offline" on to change an SSIS package, the connections for source and destination had already been build. Can I move this package to another server and execute it without going into BIDS and changing the switch back?

View 7 Replies View Related

Logging Of SSIS Package Doesnt Work When Executed As A Job

Mar 31, 2006

I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.

The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.

I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.

Thanks

View 6 Replies View Related

SQL 2012 :: SSIS - Execute Stored Procedure With Parameters Does Not Work

Jun 9, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True

When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.

SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;

However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.

I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.

Stored procedure variables:

ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

Here is my set up, what is wrong here:

I Created these Variables:

Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store

I added these parameter mappings in the Execute SQL Task

Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1

I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,

It runs the package successfully but it does not put the data into the table.

View 2 Replies View Related

Pass-thru Query Doesn't Work With A SQL 2005 SSIS Package

Dec 14, 2007

I am trying to execute an SSIS package from an MS Access 2003 database that imports a table from the Access database into a target table in SQL 2005. I saved the package in SQL 2005 and tested it out. If I run it from the Management Studio Console with Run->Execute ... everything works just fine. However, if I try to run it using the following line command "Exec master.dbo.xp_cmdshell 'DTExec /SER DATAFORCE /DTS SQL2005TestPackage /CHECKPOINTING OFF /REPORTING V'" the execution will always fail when the Access database is open (shared mode). The connection manager looks like this: "Data Source=E:Test.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Jet OLEDB:Global Bulk Transactions=1". The error is listed below:

Code: 0xC0202009
Source: NewPackage Connection manager "SourceConnectionOLEDB"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not use ''; file already in use.".


What am I doing wrong?

View 4 Replies View Related

DateAdd Expression Works In Tsql But Doesn't Work In Ssis

May 9, 2007

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?



dateadd("dd", -1, datediff("dd", 0, getdate()))



Thanks.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved