SSIS Connection String Failure
-
Hi,
I'm just starting out using packages and wonder if someone can help, I have tried search the forum for my problem but can't find a fix for it.
Basically we have a bunch of flat files that import into the database. At the moment there is no error catching, thus we spend lots of time sorting out the data that may not have failed.
I am trying to program an event handler of sorts that if a row fails to import for any reason, it gets logged into a table and goes onto the next record. I have got a script object attached to the failure component which handles the error codes etc from the import and does the insert into the table. This works fine locally when connecting to my localhost SQL Server, but when I try to connect to the live server (through IP address) I can't succeed. I assume there is a way of programming the connection from the script component Connection Manager.
I have set up a connection manager that works and try to use the following code to access it:
[font="Courier New"]
Dim myConnection As Connections
Using connection As New Data.SqlClient.SqlConnection
connection.ConnectionString = myConnection.[ConnectionName].ConnectionString.ToString
' do the update here
End Using
[/font]
However, when I run it, I get an Object not set to a reference... error.
If someone can point me in the right direction, I'd appreciate it.
-
I did a video on handling txt file errors.
Check it out if you like...
http://midnightdba.itbookworm.com/SSISTxtFileErrors/SSISTxtFileErrors.html
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
No need to set the connectionstring in code....
Why not use package configurations?
They will allow you to set the connectionstring dynamically and add the required portability to your error logging (in fact, anything on your palette is no dynamic - gotta love SSIS man)
~PD
-
Hi, I managed to get this to work so thanks for the help.
One final question, how do I log the data that failed to import?
What I want to do is log the line of data that failed so it can be emailed to us (its just text at around 500 characters).
Currently once the file is read it gets moved into the archive as it is, successful and failed data together.
Is there anyway of catching it from the error handling?
-
If you click on the file itself, you will notice a red arrow. This is the data that will be redirected that fails to import.
On the error output section of the file itself, you can specify on error that the rows need to be redirected
-
Hi crever,
I watched your video on dealing with errors on text files and I found it really useful. I have implemented a similar process into our package, i.e.
Flat File > Derived Columns > DB import (or Script Component on Error)
It's all working pretty well, the only issue left is working out the column the error occurred on. I pass across the error column, error description and 1 column which is a unique identifier. The thing I've found is that the error column is not the column that the error occurred on, but in fact a reference. This isn't much use to anyone in terms of supporting the application, so what I'd like to do is replace this with the name of the column.
Any ideas on how this is done using this technique?
-
Hey tom can you show me exactly what you're talking about and I'll wrap some gray matter around it...
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply