Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Importing a csv file into a database - inserts quotes automatically into every field Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 10:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
thanks for the help with the SSIS package.

I'm a newbie at SSIS and not sure what to enter in the variable mappings box?
Name...
Namespace...
Value Type...
Value...

also,
i'm not sure if its best to edit your solution or try and replicate what you have done on my own solution.
I have tried to drag a control flow on to the stage - is it best to drag it on to the control flow or data flow area?
Post #1406157
Posted Monday, January 14, 2013 1:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
I would strongly recomment reading through the stairways section on SSIS.

But every task is control flow.

Only task that is data flow enabled is a DFT.

I would strongly recommend trying to re-create it that way you can understand what is happening and you have a better chance at supporting the package rather than saying it works but I dont know how.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1406588
Posted Tuesday, January 22, 2013 4:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
I've had a look at the stairways section on SSIS, and re-built the package from scratch.

I can now import some of the postcode data into my SQL table successfully without the " speech marks!

However, the loop imports the same single file about 60 times and doesn't import any of the other files.
It imports the file I specify in the Flat File Connection Manager (e.g. ab.csv).

In my loop Collection properties I have specified my folder where there are about 120 csv files and in the Files box I have entered *.csv

I have created a variable name with index of 0 (but not using this variable name anywhere else in any expression).
Post #1409933
Posted Tuesday, January 22, 2013 4:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
Have you set the connection manager of the flat file source to use an expression on the connection string of the variable?

That will ensure that the filename on the connection manager changes with each file it finds.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409946
Posted Tuesday, January 22, 2013 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
In Connection Manager >> Properties >> Expressions >>
I click on the ellipses and then on the next screen on the Property column drop the list down I have chosen ConnectionString.

I can see my variable and select it, but when I click on OK I get this error message:

Expression cannot be evaluated
There was no expression to compute. An Attempt was made to compute or get the string of an empty expression.
Post #1409953
Posted Tuesday, January 22, 2013 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
can you zip up and attach your solution.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409959
Posted Tuesday, January 22, 2013 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:06 AM
Points: 27, Visits: 82
anthony.green (1/22/2013)
can you zip up and attach your solution.
Ok, all seems good apart from the new import connection manager isn't setup for a dynamic connection string.

1st off give the variable value the path to 1 of the files to import (this is just so it can get the metadata)

Then...

Single click the connection manager new import
Go to the properties window
Expressions -> elipsis
Select ConnectionString -> Expression elipsis
Expand variables
Drag down @[User::csvLoop] to the expression box

Then ok all the way out


hurray!
that works thanks a lot.

Post #1409992
Posted Tuesday, January 22, 2013 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
Not a problem, glad I could assist again.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409993
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse